CODEWITHSUNDEEP
vbaexcel
walter while
walter while

Reputation: 107

Comparing count in VBA

I have the below code that will get counts of values based on specific search criteria:

Sub WBR()
    Dim wf As WorksheetFunction
    Set wf = Application.WorksheetFunction

    With ActiveWorkbook.Worksheets("TT") 'no of tickets processed - summary
        [AE43] = wf.CountIfs(.Range("I:I"), "<>Duplicate TT", _
            .Range("G:G"), "<>Not Tested", _
            .Range("U:U"), "Item")
    End With

    With ActiveWorkbook.Worksheets("TT") 'not tested tickets - summary
        [AE44] = wf.CountIfs(.Range("G:G"), "Not Tested")
    End With

    With ActiveWorkbook.Worksheets("TT") 'Tickets moved back- outdated OS and App Versions - summary
        [AE45] = wf.CountIf(.Range("I:I"), "Outdated App Version") + wf.CountIf(.Range("I:I"), "Outdated OS")
    End With
End Sub

Now I need to do a similar function, but not sure how to do it:

How do I do this with the above code?

Upvotes: 2

Views: 91

Answers (1)

Robin Mackenzie
Robin Mackenzie

Reputation: 19289

You can just tack this on the end of your existing code. Simply take the count of COMPATIBLE in columns F and G and set the value of [AE46] according to your condition:

' compare columns F and G compatible counts
Dim lngFCompatibleCount As Long
Dim lngGCompatibleCount As Long

With ActiveWorkbook.Worksheets("TT")
    lngFCompatibleCount = wf.CountIf(.Range("F:F"), "COMPATIBLE")
    lngGCompatibleCount = wf.CountIf(.Range("G:G"), "COMPATIBLE")
End With

If lngFCompatibleCount > lngGCompatibleCount Then
    [AE46] = lngFCompatibleCount
Else 'not sure about condition where count for F= count for G
    [AE46] = lngGCompatibleCount
End If

Upvotes: 2

Related Questions