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").End(xlUp).Row
End With
2. In cell F2 there is a formula as follows:
=IF(D2="","",LOOKUP(D2,$R$2:$S$425,$S$2:$S$425))
3. How could I replace the number 425 (can be any number) with LastRow and copy paste it to the whole column F with VBA..
This is to update the formula whenever the drop down list is updated.
many thanks
2 Replies
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.
for a much better understanding of the scenario,
Whenever column D (item) is entered - its classification will be indicated at column F.
kindly see below images...
