HELP needed - VBA

[VINTSB]

Can anyone help to fix this VBA? i am trying to have the formula below from cell X3 until the last row which have the data for the row.


Range("X3").Select
LastRow = Range("X3:X" & .Rows.Count).Formula = _
"=VLOOKUP(RC[-22],'User Data File Backup Old.csv'!C2:C23,22,0)"

 

[MARK858]

Not sure that you do want your last row based on column X as you have posted (it is more usual for the column to be blank when inserting formula and the last row based on another column like column A) but it is what you asked for (except you stated Row rather than Column) so...

Sub ZZZ()
Range("X3:X" & Range("X" & Rows.Count).End(xlUp).Row).FormulaR1C1 = _
 "=VLOOKUP(RC[-22],'User Data File Backup Old.csv'!C2:C23,22,0)"
End Sub

 

[MrJoosten]

Sub insert()
Dim lr As Long
Dim rng As Range

lr = Cells(Rows.Count, 1).End(xlUp).Row
MsgBox (lr)
Set rng = Range("X3:X" & lr)
With rng
.Formula = "=VLOOKUP(RC[-22],'User Data File Backup Old.csv'!C2:C23,22,0)"
End With

End Sub

This should work if you wan to count column one. Else change rows.count to the column u want to count rows from.

 

[MARK858]

Generic method to go to the last row based on whichever is the longest column with values...

Sub Anycolumn()
Dim lr As Long
lr = Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
    Range("X3:X" & lr).FormulaR1C1 = _
    "=VLOOKUP(RC[-22],'User Data File Backup Old.csv'!C2:C23,22,0)"
End Sub