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!

 

[Jonmo1]

You have a misplaced paren )

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

 

[Sphinx404]

You have a misplaced paren )

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

Didn't like that one either. If I take out the myLastRow2 and input some random number... it works... something about adding in the myLastRow2 puts a hitch in its giddy-up I think

 

[Jonmo1]

Have you assigned a value to myLastRow2 ?
Can you post the whole code?

 

[Sphinx404]

So... yeah... Excel crashed about an hour ago and restarted with a "saved version" (which actually happens all the time for whatever reason).... This version didn't have my:

dim worksheet2 as worksheet
or
dim myLastRow2 as long

...that I added sometime prior to the crash... I didn't even notice that it was missing. I'm an idiot.

But after the readdition is seems to work great per your solution. But for here's the code anyway after I added it back in... green highlights are the commented out versions of the formula.

Sub Step4()
'sub looks compares destination to customer ID, TRUE if match, false if no match


Dim myLastRow As Long
Dim myWorksheet As Worksheet
Dim myWorksheet2 As Worksheet
Dim myLastRow2 As Long


Set myWorksheet = Worksheets("HazShipper")
myLastRow = myWorksheet.Cells(myWorksheet.Rows.Count, "A").End(xlUp).row
Set myWorksheet2 = Worksheets("DGbyFLT")
myLastRow2 = myWorksheet2.Cells(myWorksheet2.Rows.Count, "A").End(xlUp).row




ActiveWorkbook.Sheets("HazShipper").Select
Application.ScreenUpdating = False
Application.DisplayAlerts = False
    
    Range("Z2").Formula = "=LEFT(TRIM(CLEAN(Q2)),3)"
    Range("AA2").Formula = "=TRIM(CLEAN(K2))"
    Range("AB2").Formula = "=IF(ISNA(VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0)),""No ID"",VLOOKUP(TRIM(U2),DGbyFLT!$A$2:$Z$" & myLastRow2 & ",26,0))"
[COLOR=#008000]    '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)))"[/COLOR]
[COLOR=#008000]    'Range("AB2").Formula = "=IF(ISNA(VLOOKUP(Z2,AirportCodes!$A$2:$B$2000,2,0)),""No Departure"",VLOOKUP(Z2,AirportCodes!$A$2:$B$2000,2,0))"[/COLOR]
[COLOR=#008000]    'Range("AB2").Value = "=IFERROR(VLOOKUP(Z2,AirportCodes!$A$2:$C$2000,2,0),""No Departure"")"[/COLOR]
[COLOR=#008000]    Range("AC2").Formula = "=IF(LEFT(K2,4)=LEFT(AB2,4),True,False)"[/COLOR]
    Range("Z2:AC2").Select
    Selection.AutoFill Destination:=Range("Z2:AC" & myLastRow)
    
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "Customer ID"
    Range("AA1").Select
    ActiveCell.FormulaR1C1 = "HazShipper Destination"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "Airport Code Destination"
    Range("AC1").Select
    ActiveCell.FormulaR1C1 = "Dest. Match?"
    Range("Z1:AC1").Select
    Selection.Font.Bold = True
    Range("Z1,AA1").Select
    With Selection.Interior
        .Color = 65535
    End With
    Columns("Z:Z").ColumnWidth = 8.86
    Columns("AA:AC").EntireColumn.AutoFit
    Columns("Z:AC").Select
    With Selection
        .HorizontalAlignment = xlCenter
    End With
    Columns("AC:AC").ColumnWidth = 6.86
    Columns("Z:AC").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("AD2").Select


Application.ScreenUpdating = True
Application.DisplayAlerts = True


End Sub