CODEWITHSUNDEEP
vbaexcel
SM123
SM123

Reputation: 59

VBA Object Required Error when my variables are error, dont know how to fix

I am looping through named range to check if any of the cells are errors, and if yes, highlight them. The condition is dependent on two columns, and when both are errors (#NA) then the code doesnt work. How can I fix?

It gives an error when sCharacter and sCheck are

For i = 10 To 16

    sCharacter = ws_Sheet1.Range("A" & i)
    sCheck = ws_Sheet2.Range("B" & i)

    If IsError(sCharacter) And IsError(sCheck) Then
        ws_Sheet1.Range("D" & i).Interior.Color = RGB(255, 255, 0)
        ws_Sheet1.Range("E" & i).Interior.Color = RGB(255, 255, 0)

    Else
    End If

    If IsError(sCharacter) And sCheck = "Y" Then
        ws_Sheet1.Range("D" & i).Interior.Color = RGB(255, 255, 0)
        ws_Sheet1.Range("E" & i).Interior.Color = RGB(255, 255, 0)
    Else
    End If

Next i

Upvotes: 0

Views: 103

Answers (2)

TinMan
TinMan

Reputation: 7759

Retyping the variables used to check for errors to variant will allow you to use the IsError() function to check for errors. Using Cstr() to cast the variables into strings will allow you to test for a string value without throwing a Error 13 Type mismatch error.

Error Code Demo

Sub Demo()
    Dim vCharacter As Variant, vCheck As Variant
    Dim ws_Sheet1 As Worksheet, ws_Sheet2 As Worksheet
    Set ws_Sheet1 = Sheet1
    Set ws_Sheet2 = Sheet2

    For i = 10 To 16
        vCharacter = ws_Sheet1.Range("A" & i)
        vCheck = ws_Sheet2.Range("B" & i)

        If IsError(vCharacter) And (IsError(vCheck) Or CStr(vCheck) = "Y") Then
            ws_Sheet1.Range("D" & i).Resize(1, 2).Interior.Color = RGB(255, 255, 0)
        End If

    Next i
End Sub

Upvotes: 1

BigBen
BigBen

Reputation: 50008

What about something like this? (slightly revised from your original code, including changing sCharacter and sCheck to rCharacter and rCheck and declaring them each as Range).

Sub Test()
    Dim l As Long
    Dim ws_Sheet1 As Worksheet, ws_Sheet2 As Worksheet
    Dim rCharacter As Range, rCheck As Range

    Set ws_Sheet1 = Sheets("Sheet1")
    Set ws_Sheet2 = Sheets("Sheet2")

    For l = 10 To 16

        Set rCharacter = ws_Sheet1.Range("A" & l)
        Set rCheck = ws_Sheet2.Range("B" & l)

        If IsError(rCharacter) And IsError(rCheck) Then
            ws_Sheet1.Range("D" & l & ":E" & l).Interior.Color = RGB(255, 255, 0)
        End If

        If IsError(rCharacter) And rCheck.Text = "Y" Then
            ws_Sheet1.Range("D" & l & ":E" & l).Interior.Color = RGB(255, 255, 0)
        End If

    Next l
End Sub

Upvotes: 1

Related Questions