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

Sphinx404

Board Regular
Joined
May 2, 2015
Messages
186
Office Version
  1. 365
Platform
  1. Windows
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?

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

Thank you!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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?

Code:
"=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0),""No ID"",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)))"
 
Upvote 0
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.
 
Last edited:
Upvote 0
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.
Code:
"=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?
 
Upvote 0
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:

Code:
"=IFERROR(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""[COLOR=#ff0000][B]No Departure[/B][/COLOR]"")"
 
Upvote 0
Yes, it works exactly the same as IFNA. But it's not specific to only #N/A errors.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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.
Code:
"=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.

Code:
[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]
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top