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

Page 1 of 2 12 LastLast
Results 1 to 10 of 14

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

  1. #1
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

     
    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),""No Departure"")"
    Thank you!
    MS 2010, 2013, 2016 (seldom)
    Win 7

  2. #2
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,661
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

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

    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/que...xcel-2010.html
    Last edited by Joe4; Nov 29th, 2017 at 02:05 PM.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  3. #3
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Joe4 View Post
    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/que...xcel-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)))"
    MS 2010, 2013, 2016 (seldom)
    Win 7

  4. #4
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,760
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

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

    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 by Jonmo1; Nov 29th, 2017 at 02:51 PM.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  5. #5
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,661
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

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

    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?
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Jonmo1 View Post
    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),""No Departure"")"
    MS 2010, 2013, 2016 (seldom)
    Win 7

  7. #7
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    43,760
    Post Thanks / Like
    Mentioned
    17 Post(s)
    Tagged
    0 Thread(s)

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

    Yes, it works exactly the same as IFNA. But it's not specific to only #N/A errors.
    Use the MrExcel HTML Maker to post nicely formatted tables in your forum posts.
    Find a link in post number 31

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  8. #8
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Jonmo1 View Post
    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.
    MS 2010, 2013, 2016 (seldom)
    Win 7

  9. #9
    MrExcel MVP
    Moderator
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    42,661
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

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

    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.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  10. #10
    Board Regular
    Join Date
    May 2015
    Location
    In the Woods
    Posts
    149
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

      
    Quote Originally Posted by Joe4 View Post
    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:
        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)))"
    MS 2010, 2013, 2016 (seldom)
    Win 7

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com