ISNA Formula syntax in VBA

[Anne Troy]

Simply cannot figure out what's wrong. Debug just highlights the whole line. I forget when/where I need double quotes... THANKS!!

Oh, and I just need the formula!

Sub GetZips()
Dim LastRowColumnA As Long
LastRowColumnA = Cells(Rows.Count, 1).End(xlUp).Row
Range("B2:B" & LastRowColumnA).Formula = "=ISNA(VLOOKUP(A2,datatable,2,0),"Not Found",VLOOKUP(A2,datatable,2,0))"
End Sub

 

[jolivanes]

""Not Found""

This?

 

[Anne Troy]

I get this error.

 

[Peter_SSs]

Hi Anne

What version of Excel are you using? [I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)]

Do you have the IFNA() function available?

Range("B2:B" & LastRowColumnA).Formula = "=IFNA(VLOOKUP(A2,datatable,2,0),""Not Found"")"

Otherwise (In post #3 you were missing the second closing parenthesis just before ""Not Found"")

Range("B2:B" & LastRowColumnA).Formula = "=IF(ISNA(VLOOKUP(A2,datatable,2,0)),""Not Found"",VLOOKUP(A2,datatable,2,0))"

 

[Anne Troy]

This works in the cell manually:

=IF(ISNA(VLOOKUP(b4,datatable,2,0)),"Not Found",VLOOKUP(b4,datatable,2,0))

This throws method of range class failed.

Sub FormulaFill()
Dim LastRowColumnB As Long
LastRowColumnB = Cells(Rows.Count, 2).End(xlUp).Row

Range("C4:C" & LastRowColumnA).Formula = "=IF(ISNA(VLOOKUP(b4,datatable,2,0)),""Not Found"",VLOOKUP(b4,datatable,2,0))"
End Sub

Thanks for all your help!

 

[Peter_SSs]

Suggest turning Option Explicit on by default .

Then you won't get the error you have because of incorrect variable names

 

[jolivanes]

LastRowColumnB vs LastRowColumnA maybe

 

[Anne Troy]

LOL yep, I just found it, too. THANKS SO MUCH!!!

Sub FormulaFill()

Dim LastRowColumnB As Long

LastRowColumnB = Cells(Rows.Count, 2).End(xlUp).Row

Range("C4:C" & LastRowColumnB).Formula = "=IF(ISNA(VLOOKUP(b4,datatable,2,0)),""Not Found"",VLOOKUP(b4,datatable,2,0))"

End Sub

 

[Peter_SSs]

You're welcome.

 

[Anne Troy]

You're welcome.

Oops. 365!