If Error vs. On Error

[AndyTampa]

This macro is supposed to refresh a worksheet that already exists. I'm trying to error check before deleting the existing data and replacing it with new data.

I'm creating a new temporary sheet and scraping a table from a webpage. If the scrape fails, I want to delete the new sheet and display a message box., and not continue. I tried an IF Error, but it always errored. I can't find how to do "On Error, do this and this and this."

If there is no error, I want to delete the existing worksheet and rename the new worksheet. I'm familiar with adding/deleting/renaming worksheets, but it's the error handling I'm having difficulty with.

The other thing is whether there is an error or not, once it's done with the related actions, I need to end the macro. Putting EndIf in the Then or Else part of an If loop caused an issue when I tried it.

 

[6StringJazzer]

Normally you can remove any properties that you didn't actually set when recording. However, I do recommend that people understand what they are doing before they mess with something.

It won't hurt anything to leave it. It just clogs up the code and uselessly sets defaults.

 

[AndyTampa]

I'm having another problem with errors and I don't know how to get around it. It has to do with the value inside the cell.

My macro runs through each line and tests each cell to validate it. Every test seems to be working except one. For all other tests, an invalid entry is highlighted and a counter is incremented. After all tests, there is a final test which looks for blank cells and highlights them 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

This will actually re-highlight the blank cell to gray after the invalid entry counter is incremented. 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 this code, the above macro keeps crashing with Run-time error '13' Type Mismatch. I tried

On Error Resume Next

but that just allowed the cell to be changed to gray incorrectly.

What test would I use that would see these errors as the non-blank cells they are?