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.

 

[Jonmo1]

Errors in worksheet functions are handled much differently in VBA than in a formula....

If it's NA, the code is going to break and go into Debug no matter what you do..

Except, if you drop the worksheetfunction...

Here's an example of handling worksheetfunction errors...

X = Application.Vlookup(value,range,colindex,lookuptype)

Notice, theres no WorksheetFunction, it's just Application.Vlookup

Now, rather than the code stopping and debugging, it assigns the result of the vlookup to the variable X. Even if it's an Error.

Now you test X for error

X = Application.Vlookup(value,range,colindex,lookuptype)
If IsError(X) Then
     What to do on error
Else
     somerange = X
End If

Hope that helps..

 

[abhid]

the part where you wrote
"what to do on error" is what I am having issues with..

eg..
If I get N/A then I want to put "CHECK ID"
how do I do that and then get it to continue to check the other lines.
thanks so much for your help
Abhi

 

[Jonmo1]

Try this

X = Application.VLookup(Worksheets("Query from Qc Report_1").Cells(rc, 22), Range("apps"), 9, False)
With Worksheets("Query from Qc Report_1").Cells(rc, 23)
    If IsError(X) Then
        .Value = "CHECK ID"
    Else
        .Value = X
    End If
End With

 

[malfisint]

I am also trying to create something similar. I need to have a an error message when an N/A value has been created from my vlookup. Here is my original code I was using:

Dim lastv As Long
    
    Range("V1").Select
    ActiveCell.FormulaR1C1 = _
        "=VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)"
    lastv = Worksheets("Macros").Range("A1").End(xlDown).Row
        With Worksheets("Macros").Range("V1")
        .AutoFill Destination:=Range("V1:V" & lastv&)
    End With
    Columns("V:V").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

I added what I thought would be work from your previous posts but receiving an error. Can you help me rewrite this to get the error message for the N/A?

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

 

[Jonmo1]

Try

Dim lastv As Long, CustomError As String

'create your custom error message here
CustomError = "This is the message I want displayed on #N/A errors"

With Worksheets("Macros")
    lastv = .Range("A1").End(xlDown).Row
    With .Range("V1:V" & lastv)
        .FormulaR1C1 = _
        "=IF(ISNA(VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)),""" & CustomError _
        & """,VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE))"
        .Copy
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    End With
End With

 

[malfisint]

I tried that and did not receive the error message.

 

[Jonmo1]

If you didn't recieve the error, then none of the vlookups resulted in an error.

Can you post the code you just tried?

 

[malfisint]

Dim lastv As Long, CustomError As StringCustomError = "#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 = _        "=IF(ISNA(VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE)),""" & CustomError _        & """,VLOOKUP(RC[-17],'M Class Field'!R2C1:R1000C2,2,FALSE))"        .Copy        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False        Application.CutCopyMode = False    End WithEnd With</PRE>

I received an N/A value in the V column which I was expecting but did not receive the error mesage. I can attach the original spreadsheet if you would like to get a better understanding.

 

[Jonmo1]

Do you mean you want a popup error message?
Or you want that custom message in the cells?

Because this code puts the message in the cells..