CODEWITHSUNDEEP
excelerror-handlingexcel-2010vba
arooney88
arooney88

Reputation: 305

VBA Error Handling: How to bypass line that has error and stops code?

I have code that's function is to pull all loans that have "Fail" in a pass/fail column and copy criteria to another workbook. My issue is that the formula determining pass/fail requires a loan number to be in one of the criteria columns, otherwise it returns the value of "Error 2023".

With my below code I get a type mismatch error because of the "error 2023". My initial thought is to use the "On Error Resume Next" so that if my value = Error 2023 it would go to the next line and bypass that error only pulling the fails......but I'm new to error handling and not sure exactly how to write this. Is this the right way to go about this issue?

assignmentRow = 4
For x = 4 To lastRow

Windows("POC Results.xlsm").Activate

If Range("BE" & x).Value = "Fail" Then 'goes through workbook checking for "Fail"
Range("B" & x, "F" & x).Copy 'copies first criteria and pastes
Windows("Failed Audit Assigments.xlsm").Activate
Sheets("POC").Range("B" & assignmentRow).PasteSpecial Paste:=xlPasteValues

Windows("POC Results.xlsm").Activate
Range("BF" & x).Copy 'copies 2nd criteria and pastes
Windows("Failed Audit Assigments.xlsm").Activate
Sheets("POC").Range("G" & assignmentRow).PasteSpecial Paste:=xlPasteValues

assignmentRow = assignmentRow + 1
End If

Next x

Upvotes: 0

Views: 2910

Answers (2)

user4039065
user4039065

Reputation:

Subject to your recent comment to another answer, you can check for an error on an independent code line before checking to see if the cell contains Fail.

    assignmentRow = 4
    For x = 4 To lastRow
        Windows("POC Results.xlsm").Activate
        If Not IsError(Range("BE" & x).Value) Then 'check for cell error value first
            If Range("BE" & x).Value = "Fail" Then 'goes through workbook checking for "Fail"
                Range("B" & x, "F" & x).Copy 'copies first criteria and pastes
                Windows("Failed Audit Assigments.xlsm").Activate
                Sheets("POC").Range("B" & assignmentRow).PasteSpecial Paste:=xlPasteValues

                Windows("POC Results.xlsm").Activate
                Range("BF" & x).Copy 'copies 2nd criteria and pastes
                Windows("Failed Audit Assigments.xlsm").Activate
                Sheets("POC").Range("G" & assignmentRow).PasteSpecial Paste:=xlPasteValues

                assignmentRow = assignmentRow + 1
            End If
        End If
    Next x

Checking for error values in cells should stand-alone; not strung together with other criteria with an And.

Upvotes: 3

rory.ap
rory.ap

Reputation: 35280

You can use the IsEmpty() function to determine if the range is empty as a test, rather than dealing with the error.

Upvotes: 1

Related Questions