record # if data in other columns in same row

[KDS14589]

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

[snb]

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

[arnelgp]

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)

[KDS14589]

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.

[KDS14589]

arnelgp
I'm going with snb's code only because I'm trying to avoid worksheet formulas.
Thanks for the effort and response

[arnelgp]

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).

[KDS14589]

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

[Aussiebear]

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

[arnelgp]

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.

[KDS14589]

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.

[KDS14589]

THANK YOU. I will

[Paul_Hossler]

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.

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