CODEWITHSUNDEEP
excelvba
lindqmx1
lindqmx1

Reputation: 89

Run-time Error 1004 with Vlookup

I want to create the below formula in Excel using VBA:

=IF(ISERROR(VLOOKUP($F4,'17402'!$B:$BD,'17402'!AS$50,0))=TRUE,"",(VLOOKUP($F4,'17402'!$B:$BD,'17402'!AS$50,0)))

This is my code:

Dim wsFormula          As String
wsFormula = "=IF(ISERROR(VLOOKUP($F4," & "" '"" var_SN & ""'!"" & "$B:$BD," & ""'"" & var_SN & ""'!"" & "AS$50,0))=TRUE,0,(VLOOKUP($F4," & ""'"" var_SN & ""'!"" & "$B:$BD," & ""'"" var_SN & ""'!"" & "AS$50,0)))"
ActiveCell.Formula = wsFormula

Where “var_SN” is “17402”. I receive a Run-time error 1004, Application-defined or Object Define error error, so I wonder if the variable is the problem. I read about double stacking the quotes around ' and ! but that doesn't help.

I also wonder if the lookup value $F2 will become a problem for me as the row reference changes each time I run the macro. For instance, if I am in row 4, I do not want this code writing a formula that would reference cell F1 as the lookup value. Thanks much.

Upvotes: 2

Views: 126

Answers (1)

Scott Craner
Scott Craner

Reputation: 152450

You have too many "

Every where you have 17402 change to " & var_SN & ".

And the "" Needs to be """"

"=IF(ISERROR(VLOOKUP($F4,'" & var_SN & "'!$B:$BD,'" & var_SN & "'!AS$50,0))=TRUE,"""",(VLOOKUP($F4,'" & var_SN & "'!$B:$BD,'" & var_SN & "'!AS$50,0)))"

But you also do not need the IF and ISERROR. You can use the IFERROR:

"=IFERROR(VLOOKUP($F4,'" & var_SN & "'!$B:$BD,'" & var_SN & "'!AS$50,0),"""")"

The IFERROR will do the first argument unless there is an error then it does the second argument.

Upvotes: 1

Related Questions