How can I make my VBA error handling more efficient-CodePudding

I've written this for each loop that gives me the result I am looking for, but when I step through the code it seems to be repeating the error handler, and is therefore running pretty slow. Is there a way to make it run more efficiently?

The reason I needed error handling at all is because it is looping through values from a VLOOKUP and some cells contain an error.

For Each cell In maxStockRange
        On Error GoTo ErrorValue
        If cell.value = "0" Then
            cell.value = ""
        End If
ErrorValue:
            If cell.Text = "#N/A" Then
                cell.value = ""
            End If
            Resume Next
    Next cell

CodePudding user response：

Option Explicit

Sub cleanDataUsingErrorHandling()

    Dim cell As Range
    Dim maxStockRange As Range
    
    Set maxStockRange = Sheet1.Range("A1:A4")
    
    For Each cell In maxStockRange
        On Error GoTo ErrorValue
        If cell.Value = "0" Then
            cell.Value = ""
        End If
        GoTo nextIteration
        
ErrorValue:
        If cell.Text = "#N/A" Then
            cell.Value = ""
        End If
        Resume nextIteration
        
nextIteration:
    Next cell
End Sub

CodePudding user response：

Erroring Cells

Why not change the formula?

=IFERROR(IF(VLOOKUP(...)=0,"",VLOOKUP(...)),"")

Think about how you never ever want to have mixed formulas and values in a data column.

' This works for values.
maxStockRange.Replace 0, "", xlWhole
maxStockRange.Replace "#N/A", "", xlWhole

' This also works for formulas.
For Each cell In maxStockRange
    Select Case True
    Case IsError(cell), CStr(cell.Value) = "0"
        cell.Value = ""
    Case Else
    End Select
Next cell