Help With Dynamic Macros

New Contributor

Hi all, I have a first time help request, so apologies if I'm asking in the wrong place.


I have an issue with a dynamic macro I'm writing. It *should* look at the number of rows of data in column A (which is the variable and hence the reason for the dynamic macro) then infill a formula into column B (a VLOOKUP for account numbers). So far I have the below:


Sub ID_Lookup()


Dim sName As String

sName = ActiveSheet.Name

Dim LastRow As Long

With ActiveSheet.Select

.LastRow=Range("A" & Cells.Row.Count).End(x1UP).Row


End With

Application.ScreenUpdating = True

End Sub


It seems to be falling down on the entering of the formula line and I don't know why, I am getting a run-time 1004 error.


Any suggestions as to how I might fix this would be very welcome!


1 Reply


Try this:


Sub ID_Lookup()
Dim LastRow As Integer
LastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("B3:B" & LastRow).Formula = "=VLOOKUP($C3,'ParametersSheet'!$A$1:$C$270,2,FALSE)"
End Sub


Corrected a few syntax errors, typos (for example "xlUp" in stead of "x1Up") and removed the WITH statement and other unnecessary lines of code. Can't really test it as I don't have your workbook with the "ParamersSheet" in it. But the formula gets entered correctly until it reaches the bottom in Col A.