WHEN vba finds Row in header then places row number below it

[KDS14589]

ShCO01 is the worksheets CodeName
These are the variables needed

With ShCO01

Dim LVC1 As String: LVC1 = .Cells(4, Columns.Count).End(xlToLeft).Value (needed for cell contents)

‘Last Value in last header

Dim LDC1 As Long: LDC1 = .Cells(4, Columns.Count).End(xlToLeft).Column

‘Last Data Column

Dim LDR1 As Long: LDR1 = .Range(.Cells(5, "C"), .Cells(150, LDC1)).Find("*", , , , xlByRows, xlPrevious).Row

‘Last Data Row in varying range

MsgBox "Last header in 4th row is column # " & LDC1 & " (" & Split(Cells(, LDC1).Address, "$")(1) & ") with a data collection for """ & LVC1 & """ and Last Data Row is " & LDR1

End With

I have a header in row 4 that is growing in length and every once-in-awhile I add Row to my data sets.

I want the VBA code to search my header and when it finds Row it starts one row below it and places the row number down the column until the LDR1 (last data row).

My last attempt was……and of course it’s not working.

With ShCO01
Dim cll as Range

For Each cll In .Range(.Cells(4, "C"), .Cells(4, LDC1)).Cell

Select Case cll.Value = "Row"

Case True

cll.Offset(1, 0).Resize(LDR1, 0).Value = "=Row()"

Case False

cll.value = “”

End Select

Next

 End With

 

[Tetra201]

Maybe

...
For Each cll In .Range(.Cells(4, "C"), .Cells(4, LDC1))
    If cll.Value = "Row" Then
        cll.Offset(1).Resize(LDR1 - 4).Formula = "=ROW()"
        Exit For
    End If
Next cll
...

 

[jolivanes]

Or

Sub Maybe()
Dim lc As Long, lr As Long
lc = Range("XFD4").End(xlToLeft).Column
lr = ActiveSheet.UsedRange.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    With ActiveSheet.Rows(4).Find("Row", , , 1).Offset(1).Resize(lr - 4)
        .Formula = "=ROW()"
        .Value = .Value
    End With
End Sub

 

[KDS14589]

Maybe

...
For Each cll In .Range(.Cells(4, "C"), .Cells(4, LDC1))
    If cll.Value = "Row" Then
        cll.Offset(1).Resize(LDR1 - 4).Formula = "=ROW()"
        Exit For
    End If
Next cll
...

Or

Sub Maybe()
Dim lc As Long, lr As Long
lc = Range("XFD4").End(xlToLeft).Column
lr = ActiveSheet.UsedRange.Find("*", , xlValues, , xlByRows, xlPrevious).Row
    With ActiveSheet.Rows(4).Find("Row", , , 1).Offset(1).Resize(lr - 4)
        .Formula = "=ROW()"
        .Value = .Value
    End With
End Sub

jolivanes: I went with your code with a small change

lr = ActiveSheet.Range(.Cells(5, "B"), .Cells(LDR1, LDC1)).Find("*", , xlValues, , xlByRows, xlPrevious).Row

this works, but just like Tetra201 code it works only on the first Row it comes to, then stops

 

[jolivanes]

So for how many rows should this be implemented and at which row number should it start?

 

[KDS14589]

You have multiple times the word "Row" but never mentioned this. Right?

YES SORRY
But i got tetra's code to work just right by only just before you messaged by removing the 'exit for'