How to resolve #NAME? error in VBA when attempting to set a cell value using a variable in the function

Question

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!

FWIW, potential code improvements (1) Cells(i, 5).Value should be Cells(i, 5).Formula (but usually works anyway - Excel is fairly good at guessing that you want that value to be a formula) (2) Avoid using VBA function names (e.g. Month) as variable names (3) You can replace the ," & Month & ")" part of your code with ,$B$2)" (4) You can replace Cells(i, 5).Value = "=IFERROR(VLOOKUP($B" & i & ", ... with Cells(5, 5).Resize(101, 1).Formula = "=IFERROR(VLOOKUP($B5, ... and then you wouldn't need the loop - you would be assigning the formula to all 101 cells in a single statement. — YowE3K
– YowE3K, Commented Jan 9, 2018 at 19:15
LOL - I have just been cleaning up questions with the [macros] tag used incorrectly. Corrected yours and looked at the question and commented on it. The next one I looked at was posted about 30 minutes before yours, but has the identical issue as yours - writing a string into a formula without quotes around it. — YowE3K
– YowE3K, Commented Jan 9, 2018 at 19:25

Gary's Student · Accepted Answer · 2018-01-09 17:07:45Z

2

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.

answered Jan 9, 2018 at 17:07

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to resolve #NAME? error in VBA when attempting to set a cell value using a variable in the function

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related