I am building a simple macro named Update for a form control button in excel. When the button is pressed, I want a vlookup to execute in the button's sheet (named "Checker"), against the $B$7:$E$1048576 range in another sheet named "All Account Log" within the same workbook.
This vlookup formula should populate in rows 5-105 in column 5 ("E") of Checker, and for each respective row should take an input from column B to check against All Account Log's range. Here is the code I have so far:
Sub Update()
Dim Month As String
Dim i As Integer
Month = Cells(2, 2).Value
For i = 5 To 105
Cells(i, 5).Value = "=IFERROR(VLOOKUP($B" & i & ",'All Account Log'!$B$7:$E$1048576,4,0)," & Month & ")"
Next i
End Sub
If the vlookup is successful, the formula in each row of column E should pull back the respective column's data. If the vlookup does not find a match, the formula should copy the value from Cell(2,2), which is a string input by the user - a month's name. When I run the macro, the vlookup does what I want, however, when it does not find a match, the resulting formulae read "#NAME?". What can I do to fix this? I have a feeling there is some syntactical issue with how my formula references the Month variable (the value of Cell(2,2)), but I cannot figure out what the error is.
After running the macro and clicking on one of the cells with the #NAME? result, the formula in the formula bar reads: =IFERROR(VLOOKUP($B5,'All Account Log'!$B$7:$E$1048576,4,0),Jan)
Here I have the input "Jan" as the Month name, so I expect the result to be "Jan" rather than "#NAME?" Your thoughts are appreciated!
You really want:
=IFERROR(VLOOKUP($B5,'All Account Log'!$B$7:$E$1048576,4,0),"Jan")
instead of:
=IFERROR(VLOOKUP($B5,'All Account Log'!$B$7:$E$1048576,4,0),Jan)
so use:
Month = """" & Cells(2, 2).Value & """"
in the VBA code.