IsNA vlookup error in VBA need help

abhid

Board Regular
Joined
May 23, 2006
Messages
50
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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

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

Hope that helps..
 
Upvote 0
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
 
Upvote 0
Try this

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

Code:
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?

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

Rich (BB code):
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
 
Upvote 0
If you didn't recieve the error, then none of the vlookups resulted in an error.

Can you post the code you just tried?
 
Upvote 0
Code:
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.
 
Upvote 0
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..
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,269
Members
452,628
Latest member
dd2

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