Help With Dynamic Macros

Highlighted
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

.Range("B3:B"&LastRow).Formula="=VLOOKUP($C3,'ParametersSheet'!$A$1:$C$270,2,FALSE)"

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
Highlighted

@SuperNova53 

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.