CODEWITHSUNDEEP
vbaexcel
CallumDA
CallumDA

Reputation: 12113

Checking for error or vbNullString type mismatch

I have some data and I want to loop through and deal with any cells containing either nothing, or an error.

The code I tried is below and it gives me a Type mismatch because I cannot compare Error 2042 with vbNullString. 

' // doesn't work when value is #NA
For row = 1 To 10
    If Sheet1.Cells(row, 1) = vbNullString Or IsError(Sheet1.Cells(row, 1)) Then
        'do something
    End If
Next row

Firstly I tried switching the logicals around in the Or to see if it skips to do something as soon as it finds a True but it evaluates both statements first.

I have thought of a number of other ways around this but none of them seem like the best way to go about it. I'm sure there must be a simple, clean way to do this?

' Option 1
For row = 1 To 10
    If IsError(Sheet1.Cells(row, 1)) Then
        'do something
    ElseIf Sheet1.Cells(row, 1) = vbNullString Then
        'do the same stuff
    End If
Next row



'Option 2
For row = 1 To 10
    On Error Resume Next
    If Sheet1.Cells(row, 1) = vbNullString Then
        On Error GoTo 0 
        'do something
    End If  
Next row



'Option 3
Dim testValue As Variant
If IsError(Sheet1.Cells(row, 1)) Then
    testValue = vbNullString
Else
    testValue = Sheet1.Cells(row, 1)
End If

For row = 1 To 10
    If testValue = vbNullString Then
        'do something
    End If
Next row

Perhaps option 2 is the best, but it doesn't really mean much for anyone reading my code (also, I never want to lose a genuine error).

For purposes of testing you can assume my data looks something like this (A1:A10)

1
2

4
#N/A
6
#N/A

9
10

Upvotes: 2

Views: 1489

Answers (3)

Comintern
Comintern

Reputation: 22195

Just make your own data validation function and call that:

Public Function IsEmptyOrError(test As Variant) As Boolean
    If IsError(test) Then Exit Function
    IsEmptyOrError = CStr(test) = vbNullString
End Function

Calling code:

For Row = 1 To 10
    If IsEmptyOrError(Sheet1.Cells(row, 1)) Then
        'do something
    End If
Next Row

Upvotes: 2

user3598756
user3598756

Reputation: 29421

edit to add a 2nd approach

  • AutoFilter()approach:

    With Sheet1 '<--| reference your sheet
    With .Range("A1", .Cells(.Rows.Count, "A").End(xlUp)) '<--| reference its column A cells from row 1 down to last not empty one
        .AutoFilter Field:=1, Criteria1:="", Operator:=xlOr, Criteria2:="#N/A" '<--| filter referenced range on its 1st (and only) column with "" or "#N/A" values 
        If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then '<--| if any filterd cells other than header
            With .Resize(.Rows.Count - 1).Offset(1).SpecialCells(xlCellTypeVisible) '<--| reference filtered cells skipping header
                'do something with referenced range containing all wanted values
            End With
        End If
    End With
    .AutoFilterMode = False
End With

  • SpecialCells() approach:

    Dim myRng As Range
With Sheet1
    Set myRng = GetErrorsAndBlanks(.Range("A1", .Cells(.Rows.Count, "A").End(xlUp)))
    If Not myRng Is Nothing Then
        'do something with 'myRng'
    End If
End With

    where you'd use the following GetErrorsAndBlanks() function:

    Function GetErrorsAndBlanks(rng As Range) As Range
    With rng
        Set GetErrorsAndBlanks = .Resize(1, 1).Offset(.Rows.Count, .Columns.Count) '<--| initialize select range with a dummy cell, to be taken away before exiting
        On Error Resume Next
        Set GetErrorsAndBlanks = Union(GetErrorsAndBlanks, .SpecialCells(xlCellTypeConstants, xlErrors)) '<--| try and get "error" cells deriving from constants
        Set GetErrorsAndBlanks = Union(GetErrorsAndBlanks, .SpecialCells(xlCellTypeFormulas, xlErrors)) '<--| try and get "error" cells deriving from formulas
        Set GetErrorsAndBlanks = Union(GetErrorsAndBlanks, .SpecialCells(xlCellTypeBlanks)) '<--| try and get "blank" cells
        Set GetErrorsAndBlanks = Intersect(GetErrorsAndBlanks, .Cells) '<--| take "dummy" cell away
    End With
End Function

Upvotes: 1

SJR
SJR

Reputation: 23081

I would go with option 1 but you could try this instead. Btw don't use Row as a variable name as it's a VBA property. (And don't ask me why this works when If doesn't.)

Sub x()

Dim Row1 As Long

For Row1 = 1 To 10
    Select Case True
        Case IsError(Sheet1.Cells(Row1, 1)), Sheet1.Cells(Row1, 1) = vbNullString
        'do something
    End Select
Next Row1

End Sub

Upvotes: 1

Related Questions