Jan 24 2020 01:42 AM
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! :)
Jan 24 2020 04:46 AM - edited Jan 24 2020 04:50 AM
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.