CODEWITHSUNDEEP
excelvba
Geographos
Geographos

Reputation: 1456

VBA Excel If statement returns wrong answer

I prepared the if statement for checking my cells in the specific row. I have several cells, which I have to check. Their values are mostly "x" but sometimes they vary.
The problem is, that even if one of the value is different than "x", I am still getting the msgbox, that everything is good as per the code, which I prepared.

Sub AuditCheck()
    If Range("C33,C39:C40,C43,C53:C54,C57:C59,C68").Value = "x" Or Range("C33,C39:C40,C43,C53:C54,C57:C59,C68").Value = "0" Then
        'Rows(39).Delete
         Range("C58").Activate  'taking a look just in case
         MsgBox ("All good!") ' if so we can hide X
         ActiveSheet.Range("$A$5:$IF$77").AutoFilter Field:=1, Criteria1:="<>x", Criteria2:="<>0"
    Else
        MsgBox ("You have to change the pricing!")
        If Range("C39").Value <> "x" Then       'C39 CASE
            MsgBox ("Install duct Upturns/upturn section must be removed!")
            Call RemoveUpturn
            Call New_version_upturn
        End If
        Exit Sub   'the macro is terminated because you have to change the prices
    End If
End Sub

enter image description here

Is there something, which I haven't mentioned in the code?

Upvotes: 0

Views: 317

Answers (2)

FaneDuru
FaneDuru

Reputation: 42236

Try the next code, please:

Sub AuditCheck()
 Dim sh As Worksheet, rng As Range, ar As Range, countX As Long, zCount As Long
 Set sh = ActiveSheet
 Set rng = Range("C33,C39:C40,C43,C53:C54,C57:C59,C68")
   For Each ar In rng.Areas
    countX = countX + WorksheetFunction.CountIf(ar, "x")
    zCount = zCount + WorksheetFunction.CountIf(ar, "0")
   Next
   If countX = rng.cells.count Or zCount = rng.cells.count Then 'here, you maybe want adding the two counts...
       Range("C58").Activate  'taking a look just in case
       MsgBox ("All good!") ' if so we can hide X
       ActiveSheet.Range("$A$5:$IF$77").AutoFilter field:=1, Criteria1:="<>x", Criteria2:="<>0"
   Else
        MsgBox ("You have to change the pricing!")
        If Range("C39").Value <> "x" Then       'C39 CASE
            MsgBox ("Install duct Upturns/upturn section must be removed!")
            Call RemoveUpturn
            Call New_version_upturn
        End If
   End If
End Sub

It looks strange checking of counted "x" or "0". If you wanted to count them together, you should add them an compare with total range cells count...

If counting zero does not count (anymore), you just delete the second condition.

Upvotes: 1

TourEiffel
TourEiffel

Reputation: 4414

You must use CountIf (doc here) which will counts the number of cells within a range that meets the given criteria to do that you would have done something like that :

Sub Try_Me()
    Dim Myrng As Range
    Dim NumCheck as Long
    Dim StrCheck as String
    StrCheck = "x"
    NumCheck = 0
    Set Myrng = Range("C33,C39:C40,C43,C53:C54,C57:C59,C68")
    If WorksheetFunction.CountIf(Myrng, NumCheck ) = Myrng.Count Or WorksheetFunction.CountIf(Myrng, StrCheck ) = Myrng.Count Then  
        Range("C58").Activate  'taking a look just in case
        MsgBox ("All good!") ' if so we can hide X
        ActiveSheet.Range("$A$5:$IF$77").AutoFilter Field:=1, Criteria1:="<>x", Criteria2:="<>0"
    Else 
        MsgBox ("You have to change the pricing!")
        If Range("C39").Value <> "x" Then       'C39 CASE
            MsgBox ("Install duct Upturns/upturn section must be removed!")
            Call RemoveUpturn
            Call New_version_upturn
        End If
        Exit Sub   'the macro is terminated because you have to change the prices
    End If
End Sub

EDIT According to @chris neilsen you should do as below since CountIf does not work with non-contiguous range. So I would suggest you to just count the number of X in your range if it does match with the excepted number of x or 0 the the if condition will return true :

Sub Try_Me()
    Dim Myrng As Range
    Dim NumCheck as Long
    Dim StrCheck as String
    Dim NumExceptedX as Int
    Dim NumeExceptedZ
    NumExceptedX = 11
    NumeExceptedZ = 15
    StrCheck = "x"
    NumCheck = 0
    Set Myrng = Range("C33:C68")
    If WorksheetFunction.CountIf(Myrng, NumCheck ) = NumeExceptedZ  Or WorksheetFunction.CountIf(Myrng, StrCheck ) = NumExceptedX  Then  
        Range("C58").Activate  'taking a look just in case
        MsgBox ("All good!") ' if so we can hide X
        ActiveSheet.Range("$A$5:$IF$77").AutoFilter Field:=1, Criteria1:="<>x", Criteria2:="<>0"
    Else 
        MsgBox ("You have to change the pricing!")
        If Range("C39").Value <> "x" Then       'C39 CASE
            MsgBox ("Install duct Upturns/upturn section must be removed!")
            Call RemoveUpturn
            Call New_version_upturn
        End If
        Exit Sub   'the macro is terminated because you have to change the prices
    End If
End Sub

Upvotes: 1

Related Questions