CODEWITHSUNDEEP
vbaexcel
barciewicz
barciewicz

Reputation: 3783

If And not working

The following code excerpt is meant to change a cell's style to "Bad" if the cell has a formula error "#REF!" and the cell 3 columns left to it HAS NOT "Gesamtergebnis" text in it. But why does the following code color also the cell that HAS "Gesamtergebnis" written three cells left of it, as if the second part of "AND" clause was ignored?

For Each cell In Final.Worksheets("PIVOT").UsedRange.Cells.SpecialCells(xlFormulas)
    If cell.Value = "Error 2023" And cell.Offset(0, -3).Value = "Gesamtergebnis" Then cell.Style = "Bad"
Next cell

Thanks, Bartek

Upvotes: 0

Views: 73

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149295

The better way to handle your case is to use SpecialCells with the parameter 16. SpecialCells with these arguments lets you select the errors in formulas within a range.

Is this what you are trying?

Sub Sample()
    Dim ErrRange As Range
    Dim rng As Range
    Dim aCell As Range

    Set rng = Final.Worksheets("PIVOT").UsedRange.Cells

    On Error Resume Next
    Set ErrRange = rng.SpecialCells(xlCellTypeFormulas, 16)
    On Error GoTo 0

    If Not ErrRange Is Nothing Then
        For Each aCell In ErrRange
            If aCell.Offset(, -3).Value = "Gesamtergebnis" Then aCell.Style = "Bad"
        Next aCell
    End If
End Sub

Screenshot

I am using aCell.Interior.ColorIndex = 3 instead of aCell.Style = "Bad" for demonstration purpose below. It colors the cell red.

enter image description here

Upvotes: 3

Storax
Storax

Reputation: 12167

Try the following approach

Sub ErrTest()
    Dim cell As Range
    Dim errval As String
    Set cell = Range("A1")
    If IsError(cell) Then
        errval = cell.Value
        Select Case errval
            Case CVErr(xlErrDiv0)
                MsgBox "#DIV/0! error"
            Case CVErr(xlErrNA)
                MsgBox "#N/A error"
            Case CVErr(xlErrName)
                MsgBox "#NAME? error"
            Case CVErr(xlErrNull)
                MsgBox "#NULL! error"
            Case CVErr(xlErrNum)
                MsgBox "#NUM! error"
            Case CVErr(xlErrRef)
                MsgBox "#REF! error"
            Case CVErr(xlErrValue)
                MsgBox "#VALUE! error"
            Case Else
                MsgBox "This should never happen!!"
        End Select
    End If
End Sub

Here is the docu https://msdn.microsoft.com/en-us/vba/excel-vba/articles/cell-error-values

For your situation you could use the following function

Function refError(cell As Range) As Boolean
    Dim errval As Variant
    If IsError(cell) Then
        errval = cell.Value
        If errval = CVErr(xlErrRef) Then refError = True
    End If
End Function

And your code would be then like

For Each cell In Final.Worksheets("PIVOT").UsedRange.Cells.SpecialCells(xlFormulas)
    If refError(cell) And cell.Offset(0, -3).Value = "Gesamtergebnis" Then cell.Style = "Bad"
Next cell

Upvotes: 1

Related Questions