Forum Discussion
VBA to update the formula in a cell based on last data in a drop down list
1. In column R of my worksheet is a drop down list of items. I can determine its last row of data by: Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "R").En...
With ActiveSheet
LastRow = .Cells(.Rows.Count, "R").End(xlUp).Row
End With
UpdatedFormula = "=IF(D2="""","""",LOOKUP(D2,$R$2:$S$" & Trim(Str(LastRow)) & ",$S$2:$S$" & Trim(Str(LastRow)) & "))"
Range("F2").Select
Range("F2") = UpdatedFormula
Selection.Copy
Range("F2:F1001").Select
ActiveSheet.Paste
Application.CutCopyMode = False
After so much 'blood and sweat' - I came up with the solution on how to replace 425 with LastRow, though it looked "unprogramly", it gets the job done!
I would like to share the above to anyone who might have the same problem.