Another Error Checking Question

[AndyTampa]

I tried asking this in my previous post On Error vs. If Error, but nobody is watching that conversation any longer since I got a resolution. I asked this related question without getting a response. Please forgive me if this violates the duplicate posting rule. It's similar, but not identical.

I'm having another problem with errors in cells. My macro runs through each line and tests each cell to validate that the value is appropriate for the cell. For all tests, an invalid entry is highlighted in pink and a counter is incremented. After all tests, there is a final test which looks for blank cells and changes the highlight to gray.

For Each Cell In Range("A" & RNum & ": E" & RNum)
    If Cell.Value = "" Then
    Cell.Interior.ColorIndex = 15
    BLKErr = BLKErr + 1
    End If
 Next Cell

However, the macro breaks if there is an error in a cell. So if someone puts -Abc in a cell that is supposed to be a number or a date, the result in the cell is #NAME?. Since that is an invalid entry, my macro highlights it pink and increments the counter but then when it gets to the code above, the macro crashes with Run-time error '13' Type Mismatch. I figured out that On Error Resume Next and a test for IsEmpty both allowed the cell to be changed to gray when it shouldn't be since it isn't empty. I got this to work:

    For Each Cell In Range("A" & RNum & ": E" & RNum)
    If IsError(Cell.Value) = False Then
        If Cell.Value = "" Then
        Cell.Interior.ColorIndex = 15
        BLKErr = BLKErr + 1
        End If
    End If
    Next Cell

However, this #NAME? error is breaking the macro in another places where my solution isn't working.

    If Cells(RNum, 3).Value = "" Or Cells(RNum, 3).Value = 0 Or IsNumeric(Cells(RNum, 3).Value) = False Or IsError(Cells(RNum,3).Value) = True Then
        Cells(RNum, 3).Interior.ColorIndex = 22
        InvEnt = InvEnt + 1
    End If

If the cell is blank or 0 or isn't numeric or is an error, then highlight the cell. I pulled out the IsError portion and still got the mismatch error. What am I mismatching?

 

[Jeffrey Mahoney]

Tests for isnumeric on a cell with an error will trigger a runtime error. For an IF statement with multiple IF with OR, all will be tested even if one of them is true. The code below works for me.

Sub TestErr()

  Dim Rnum As Long
  Dim InvEnt As Long
  Dim Var As Variant
 
  Rnum = 3
  Var = Cells(Rnum, 3).Value
  If IsError(Var) = True Then
    Cells(Rnum, 3).Interior.ColorIndex = 22
    InvEnt = InvEnt + 1
  ElseIf Var = "" Or Var = 0 Or IsNumeric(Var) = False Then
    Cells(Rnum, 3).Interior.ColorIndex = 22
    InvEnt = InvEnt + 1
  End If
End Sub

 

[AndyTampa]

That's great! I'll use that, but I'd like to understand it a little better.

Q1) Is it required to set a variant or can I still use the cell references inside the IsError and IsNumeric functions?

Q2) With the error being a Type Mismatch, what is mismatching?

I'd like to know for the future so I can fix similar issues when they appear.

 

[Jeffrey Mahoney]

Variants can store text, numbers and cell errors. I used the variant variable because I didn't want to have to keep on pulling the cell value over and over again.

Type mismatch occurred because you were trying to compare a cell error to a string and a number. I forgot what TYPE a cell error is, but they're not compatible with normal cell values.

Let me know if that helped

 

[Jeffrey Mahoney]

I use temporary variant variables so I can test for errors, then I convert the values into other variables for permanent storage. You don't have to use a variant for testing the error, but you do need to put that test on a different line, The error test can't be blended with other data types in the same if statement.

 

[AndyTampa]

That worked. My macro is testing on on each cell over 6 columns. To redefine the variable each time would be like doing it for almost every cell and would make troubleshooting more of a pain than it is now, but I see its value. I'm just surprised I can't test for all those things in a single statement. I'm basically saying, "this cell has to be a number above or below zero and nothing else; not a letter, not empty, not an error."

Thank you for the help.

I just realized that I'm not testing for formulas in those cells. I can't allow those either. Is there a special property about cell.HasFormula that will conflict with one of the other types in my macros?

 

[AndyTampa]

In my macro, I use this code to capitalize the text in each text cell of a row.

'-------------------------------
'| UPPERCASE COLUMNS A, D, & G |
'-------------------------------
    For Each Cell In Range("A" & RNum & ", D" & RNum & ", G" & RNum)
        Cell.Value = UCase(Cell)
    Next Cell

In a similar thought process, I just tried this code that turns formulas to values.

'-------------------------------
'| CONVERT FORMULAS TO VALUES  |
'-------------------------------
    For Each Cell In Range("A" & RNum & ": G" & RNum)
        If Cell.HasFormula = True Then Cell.Formula = Cell.Value
    Next Cell

It seems to nullify the effect of the #NAME? errors by turning them into text which will fail the other tests. This could streamline my macro by removing those extra tests. Do you agree?

I'd really rather test for formulas and mark them as invalid data so a formula can't suddenly bill someone $46,000 if a user pointed an adjustment to a Date cell. If I simply change it to a specific value, the subsequent tests should do the rest without the need for testing IsError at all. What do you think about that?

'-------------------------------
'| CONVERT FORMULAS TO VALUES  |
'-------------------------------
    For Each Cell In Range("A" & RNum & ": G" & RNum)
        If Cell.HasFormula = True Then  Cell.Value = "Void"
    Next Cell

I could then test for the text if I need to. Do you foresee any issue with this?