0

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
Improve this question
4
  • What is the error message? Commented Dec 31, 2014 at 14:16
  • It's a run time error '13' type mismatch. But when I go into my VBA code and highlight what the value is determining if it matches "Fail" it says Error 2023. Commented Dec 31, 2014 at 14:20
  • 2
    Did you try changing .Value to .Text since you are matching against a string? Commented Dec 31, 2014 at 14:22
  • @jbarker2160 the .Text change also worked as well. Thanks Commented Dec 31, 2014 at 15:01

2 Answers 2

Reset to default
3

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

@arooney88 - Remember that string comparisons in VBA default as case-sensitive; e.g. fail ≠ Fail.
Thanks! This worked and was in the same logic that I was thinking about.
1

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

Improve this answer

1 Comment

I input On Error Resume Next above the If Range("BE" & X".Value = "Fail" Then and that just pulled all the loans that had the REF! error and not the right Fail loans. Is there a way to have it if the cell.value = an error it will iterate to next x and continue down?

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.