Macro in 2013 Working - 2010 Not Working: #NAME?

[Sphinx404]

Does anyone see something in this formula that would through a #NAME? error at me?

I've created this macro in Excel 2013, but will end up be utilized in 2010. It runs perfect on my machine, but throws errors in these simple formulas in 2010. I know what the error means, but I don't see what "text in formula" is it not recognizing? Is there another way of inputting this highlighted text so it's compatible in 2010?

Ideas?

Range("AB2").Formula = "=IFNA(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""[COLOR=#ff0000][B]No Departure[/B][/COLOR]"")"

Thank you!

 

[Joe4]

IFNA was introduced in 2013. If you want to use it for earlier versions, you need to use IF(ISNA... as shown here: https://www.experts-exchange.com/qu...ow-do-I-do-an-IFNA-formula-in-Excel-2010.html

 

[Sphinx404]

IFNA was introduced in 2013. If you want to use it for earlier versions, you need to use IF(ISNA... as shown here: https://www.experts-exchange.com/qu...ow-do-I-do-an-IFNA-formula-in-Excel-2010.html

Thanks for the heads up @Joe4 I didn't realize. That is going in the knowledge bucket.

My problem now is with the following. I got some of my formulas fixed, but when it comes to adding in "myLastRow" I'm getting stuck. Excel does not like the way I have it written....

Ideas?

"=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0),""No ID"",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)))"

 

[Jonmo1]

You could change the IFNA to IFERROR
It works the same as IFNA, but is not restriced to just #N/A errors. It would capture any error.

 

[Joe4]

My problem now is with the following. I got some of my formulas fixed, but when it comes to adding in "myLastRow" I'm getting stuck. Excel does not like the way I have it written....

Assuming that myLastRow2 is returning a valid number, I don't see any errors with that part. It may have more to do with your text qualifiers around "No ID".
I hate doubling or tripling up double-quotes. I use Chr(34) instead when I want literal text, i.e.

"=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)," & Chr(34) & "No ID" & Chr(34) & ",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)))"

Does that work any better?

 

[Sphinx404]

You could change the IFNA to IFERROR
It works the same as IFNA, but is not restriced to just #N/A errors. It would capture any error.

So essentially I could just use:

"=IFERROR(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""[COLOR=#ff0000][B]No Departure[/B][/COLOR]"")"

 

[Jonmo1]

Yes, it works exactly the same as IFNA. But it's not specific to only #N/A errors.

 

[Sphinx404]

Yes, it works exactly the same as IFNA. But it's not specific to only #N/A errors.

Thanks @Jonmo1 I'll definitely use the easier of the 2 functions... just need to verify the ones where identifying the #N/A is necessary.

 

[Joe4]

It works the same as IFNA, but is not restriced to just #N/A errors. It would capture any error.

Just be VERY careful with that. That part highlighted in red should not be trivialized. It does much more than IFNA, it ignores all errors for any reason.
IFNA handles just no match, but IFERROR will ignore everything, such as if you have a typo in your formula (i.e. if you spelled "VLOOKUP" as "VLOKUP").
So if you see "No Departure", you really cannot be certain if it is because there is no match, or there is a syntax error in your formula.

 

[Sphinx404]

Assuming that myLastRow2 is returning a valid number, I don't see any errors with that part. It may have more to do with your text qualifiers around "No ID".
I hate doubling or tripling up double-quotes. I use Chr(34) instead when I want literal text, i.e.

"=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)," & Chr(34) & "No ID" & Chr(34) & ",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)))"

Does that work any better?

Didn't like it. 1004 Error.

[SIZE=2]    Range("AB2").Formula = "=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)," & Chr(34) & "No ID" & Chr(34) & ",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)))"[/SIZE]