IsNA vlookup error in VBA need help

[abhid]

I have this code that does a simple vlookup and populates the values (just like the formula in excel) HOWEVER, it goes line by line in VBA and one value had an #N/A and the code broke.
How do I get it to continue and if it does not find the vlookup it either puts and #N/A or colors it red or anything to highlight an #n/a and continues to populate the file.

any help!!!!
thanks
code below.
"Query from Qc Report_1" is the column with ID#'s and next to it I want a vlookup done to
"apps" where the data is


Worksheets("Query from Qc Report_1").Cells(rc, 23).Value = Application.WorksheetFunction.IsNA(WorksheetFunction.VLookup(Worksheets("Query from Qc Report_1").Cells(rc, 22), Range("apps"), 9, False)

where do I put the if-then stmt
so if it is N/A then put "not found" and continue down the lines.

 

[malfisint]

I apologize for the confusion. I would like a pop up message.

 

[Jonmo1]

so to be clear...

You want the standard #N/A erros to still appear in the cells..
But at the end, give a popup error indicating there is at least one of those errors?

Try

Dim lastv As Long, CustomError As String
CustomError = "#N/A value was created for class field.  Please edit and create file again."
With Worksheets("Macros")
    lastv = .Range("A1").End(xlDown).Row
    With .Range("V1:V" & lastv)
        .FormulaR1C1 = _
        "=VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        If IsError(Application.Sum(.Cells)) Then MsgBox CustomError
    End With
End With

 

[malfisint]

That worked perfectly!! One last question and I apologize for not asking earlier but how can I abort any further code being executed and stop any other macros being executed if an N/A value is present?

 

[Jonmo1]

Try

Dim lastv As Long, CustomError As String
CustomError = "#N/A value was created for class field.  Please edit and create file again."
With Worksheets("Macros")
    lastv = .Range("A1").End(xlDown).Row
    With .Range("V1:V" & lastv)
        .FormulaR1C1 = _
        "=VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        If IsError(Application.Sum(.Cells)) Then 
            MsgBox CustomError
            Exit Sub
        End If
    End With
End With

 

[malfisint]

So close!! I received a compile error: Expected End With. I am going to enter the rest of the code I have so that it makes more sense and probably easier to troubleshoot. I am getting the error at the very last End Sub

'Create Class Field
    
   Dim lastv As Long, CustomError As String
    CustomError = "#N/A value was created for class field.  Please edit and create file again."
    With Worksheets("Macros")
    lastv = .Range("A1").End(xlDown).Row
    With .Range("V1:V" & lastv)
        .FormulaR1C1 = _
        "=VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
        If IsError(Application.Sum(.Cells)) Then
            MsgBox CustomError
            Exit Sub
        End If
    End With
    
    'Create the Quantity thru Merchant Fields
    
    Dim lastdec As Long
    
    Columns("I:I").Select
    Selection.Copy
    Columns("W:W").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("X1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(LEFT(RC[-15],11)=""QTY: 000.00"",""0"",IF(LEFT(RC[-15],1)=""Q"",IF(MID(RC[-15],6,1)=""0"",LEFT(RC[-15],12),RC[-15]),""""))"
    lastdec = Worksheets("Macros").Range("A1").End(xlDown).Row
        With Worksheets("Macros").Range("X1")
        .AutoFill Destination:=Range("X1:X" & lastdec&)
    End With
    Columns("X:X").Select
    Selection.Copy
    Columns("W:W").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("X:X").Select
    Selection.Delete Shift:=xlToLeft
    Columns("W:W").Select
    Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
        Array(7, 1), Array(8, 1)), TrailingMinusNumbers:=True
    Columns("W:W").Select
    Selection.Delete Shift:=xlToLeft
    Columns("X:X").Select
    Selection.Delete Shift:=xlToLeft
    Columns("Y:AC").Select
    Selection.Delete Shift:=xlToLeft
    Range("W:W").Select
    Selection.NumberFormat = "0.00"
    
    
    'Format decimal from three to two for Quantity
    Dim lastnum As Long
    
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=FIXED(RC[-3],2,)"
    lastnum = Worksheets("Macros").Range("A1").End(xlDown).Row
        With Worksheets("Macros").Range("Z1")
        .AutoFill Destination:=Range("Z1:Z" & lastnum&)
    End With
    Columns("Z:Z").Select
    Selection.Copy
    Columns("W:W").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Columns("Z:Z").Select
    Selection.Delete Shift:=xlToLeft
    
    'Unit Cost Fields
    
    Dim lastdiv As Long
    
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "=IF(RC[-2]=""0.00"",""0"",FIXED(RC[-17]/RC[-2],2,))"
    lastdiv = Worksheets("Macros").Range("A1").End(xlDown).Row
        With Worksheets("Macros").Range("Y1")
        .AutoFill Destination:=Range("Y1:Y" & lastdiv&)
    End With
    Columns("Y:Y").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    
    'Correct Merchant Name
    Dim lastif As Long
    
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = _
        "=IF(RC[-2]="""","""",IF(RC[-2]=""EZ"",""EZ MART"",IF(RC[-2]=""WTG"",""WTG FUEL"",IF(RC[-2]=""PHILL"",""PHILL 66"",IF(RC[-2]=""CIRCLE"",""CIRCLE K"",IF(RC[-2]=""MURPHY"",""MURPHY U"",IF(RC[-2]=""WEST"",""WEST TEX"",RC[-2])))))))"
    lastif = Worksheets("Macros").Range("A1").End(xlDown).Row
        With Worksheets("Macros").Range("Z1")
        .AutoFill Destination:=Range("Z1:Z" & lastif&)
    End With
    Columns("Z:Z").Select
    Selection.Copy
    Columns("X:X").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Columns("Z:Z").Select
    Selection.Delete Shift:=xlToLeft
    
    'This portion of the subroutine adds "~" and saves to previously made text file-LBW 10/17/2012 added .text to remove runtime error 13
    For Each rngRow In ActiveSheet.UsedRange.Rows
    For Each cell In rngRow.Cells
    CSV_Line = CSV_Line & "~" & Trim(cell.Text)
    Next
    Print #DestNum, Mid(CSV_Line, 2): CSV_Line = vbNullString
    Next
    Close #DestNum
    Cells.Select
    Selection.ClearContents
    Range("A1").Select
        
    If ErrorCounter > 0 Then
        Call MsgBox(Str(ErrorCounter) & " deposits were not processed due to missing key information.", vbDefaultButton1, "Deposit Processing Errors")
    Else
        Call MsgBox("The process successfully finished.", vbDefaultButton1, "Deposits Processed")
    End If
    
died:
    
End Sub

 

[Jonmo1]

You left out the 2nd End With at the end of the code I posted.

 

[malfisint]

DUH!! Wow...that is terrible. You are amazing!! thank you so so much for all of your help and patience!! I really appreciate it!!

 

[Jonmo1]

Glad to help, thanks for the feedback.