Consulting

Results 1 to 12 of 12

Thread: record # if data in other columns in same row

  1. #1
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location

    record # if data in other columns in same row

    The record data for my worksheet (ShC012) is placed in non-adjacent cells, such as C5, E5, Z5, but in the same row. This placement varies up to Column CC (for now but that can expand), I have 1 record that has the only data in CA53. I wish to have in column B of each row with data to have a record # based on the row number but in the format of 5 0’s and with a leading CO, like data in row 35 will have CO00035 in cell B35. The rows with no data will be blank. Data’s in column A is not to be altered, that data rows vary from A2 to A100. I’ve attached a workbook with no sensitive data that I use for demo’s and the example is on tab ‘Records’

    My last FAILED attempt at code was (I hadn’t got to the format part)

    Dim ws As Worksheet
     Set ws = ShCO12
    Dim row As Long
    Dim col As Long
    Dim last_row As Long
    Dim last_col As Long
    last_row = Range("B4").End(xlDown).row
    last_col = ws.Cells(3, Columns.Count).End(xlToLeft).COLUMN
    For last_row = 4 To Range("B4").End(xlDown).row
    For last_col = 3 To ws.Cells(4, Columns.Count).End(xlToLeft).COLUMN
    For row = 4 To last_row
     
    If ws.Cells(row, last_col).COLUMN <> "" Then
    ws.Cells(row, 2).Value = ws.Cells(row, 2).row
    End If
    Next row
    Attached Files Attached Files

  2. #2
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Sub M_snb()
      sn = Sheet6.UsedRange
       
      For j = 1 To UBound(sn)
        If UBound(Split(Application.Trim(Join(Application.Index(sn, j))))) > 0 Then sn(j, 2) = Format(j, "\C\O00000")
      Next
       
      Sheet6.UsedRange = sn
    End Sub

  3. #3
    you can also create a function in a Module:

    Public Function ColumnData(ByVal rw As Long) As Long
        ColumnData = Cells(rw, Columns.Count).End(xlToLeft).Column
    End Function
    then use it in a Formula (see formula in Column B of Record sheet)
    Attached Files Attached Files

  4. #4
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location
    Quote Originally Posted by snb View Post
    Sub M_snb()
      sn = Sheet6.UsedRange
       
      For j = 1 To UBound(sn)
        If UBound(Split(Application.Trim(Join(Application.Index(sn, j))))) > 0 Then sn(j, 2) = Format(j, "\C\O00000")
      Next
       
      Sheet6.UsedRange = sn
    End Sub

    I'm going with your code THANKS but I'm getting both SN & j need to be variables declared. And the codename for the sheet is ShCO12 (worksheet names tend to change in design mode) if that matters.

  5. #5
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location
    arnelgp
    I'm going with snb's code only because I'm trying to avoid worksheet formulas.
    Thanks for the effort and response

  6. #6
    ok if you want to go that path, it's fine.
    i only want to share you what i have using
    vba. i created a Public sub addRowNumber() in Module6.
    it will put record# to sheet "Record (2)" (for demo).
    Attached Files Attached Files

  7. #7
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location

    THANKS

    Quote Originally Posted by arnelgp View Post
    ok if you want to go that path, it's fine.
    i only want to share you what i have using
    vba. i created a Public sub addRowNumber() in Module6.
    it will put record# to sheet "Record (2)" (for demo).
    Arnelgp
    I hope I don’t get in trouble with the forum moderator but I haven’t gotten a response from other and I don’t have any patience. I tried your new code and it works just as I wanted and placed it in my real-world worksheet with one modification (worksheet name). THANKS
    Question though….I use worksheet code names instead of the worksheet name and may use this on other sheets with same format. How would I adapt the code?
    Again THANKS

  8. #8
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Quote Originally Posted by KDS14589 View Post
    I hope I don’t get in trouble with the forum moderator but I haven’t gotten a response from other and I don’t have any patience.
    Duly noted, but it doesn't excuse you for not indicating the other link
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  9. #9
    i created a button on Record and Record (2) worksheet to call the sub.
    you can do the same with the other worksheets.
    if you want to numbering to start other than row 1 and column 1, you must
    supply it. otherwise all parameters are optional.
    Attached Files Attached Files

  10. #10
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location
    What link? The other is from this forum and this thread. Maybe a [QUOTE] [QUOTE] but that response would have gone to the other, I think, what I did was only way I know. Again I apologize for doing something wrong.

  11. #11
    VBAX Regular
    Joined
    Sep 2021
    Location
    Cedar Falls, IA
    Posts
    18
    Location
    THANK YOU. I will

  12. #12
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    [QUOTE=KDS14589;415283]What link? The other is from this forum and this thread. Maybe a [QUOTE]
    but that response would have gone to the other, I think, what I did was only way I know. Again I apologize for doing something wrong.
    Probably a simple misunderstanding

    If someone posts the same question on another forum (#4 in my signature) we like to also have a link so that we know that your issue is resolved and we don't waste time. So we usually use the term 'link' for such things, and not to refer to posts within the same thread, which I believe was your intention

    The other is from this forum and this thread
    If you were going to refer to another thread here, adding a 'link' makes it easy to refer to it for additional information

    So something like "I'm going with snb's code in post #2 since ...." would be clear
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •