AndyTampa
Board Regular
- Joined
- Aug 14, 2011
- Messages
- 199
- Office Version
- 365
- 2016
- Platform
- Windows
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.
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:
However, this #NAME? error is breaking the macro in another places where my solution isn't working.
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?
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.
VBA Code:
For Each Cell In Range("A" & RNum & ": E" & RNum)
If Cell.Value = "" Then
Cell.Interior.ColorIndex = 15
BLKErr = BLKErr + 1
End If
Next Cell
VBA Code:
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
VBA Code:
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?