Count if function not working properly in vba

Question

I'm having some trouble getting my macro to use a countif funtion to display the frequency of scores in a given cell. This is part of a larger macro that I am currently working on to generate a report out of a given set of exported data.

When I try to run the code, it returns all zeros in the cells I have specified even though there is data in there that matches my criteria.

Please feel free if you like to critique this code as I am just starting out in programming and wanting to learn as much as possible.

Thanks in advance!

Here is a copy of the code:

Dim i As Integer
Dim ws_raw As Worksheet
Dim ws_rpt As Worksheet

Set ws_raw = Sheets("Raw Data")
Set ws_rpt = Sheets("Report")

If ws_raw.Range("H2") <> "" Then

    i = WorksheetFunction.CountIf(Range("S2:CCC2"), "5")
    ws_raw.Range("I2").Value = i

    i = WorksheetFunction.CountIf(Range("S2:CCC2"), "6")
    ws_raw.Range("J2").Value = i

    i = WorksheetFunction.CountIf(Range("S2:CCC2"), "7")
    ws_raw.Range("K2").Value = i

    i = WorksheetFunction.CountIf(Range("S2:CCC2"), "8")
    ws_raw.Range("L2").Value = i

Else

End If

score 4 · Accepted Answer · 2016-09-14 14:46:16Z

Try it as,

i = WorksheetFunction.CountIf(Range("S2:CCC2"), 5)

Text-that-looks-like-a-number is not the same thing as a number; e.g. 5<>"5".

On a related note, explicitly referencing the .Parent worksheet is widely considered 'best practise'. A With ... End With statement not only cleans up your code but speeds it up. I also prefer using the Excel Application object over the of the WorksheetFunction object as any error can be returned to a variant.

Dim i As Variant
Dim ws_raw As Worksheet, ws_rpt As Worksheet

Set ws_raw = Sheets("Raw Data")
Set ws_rpt = Sheets("Report")

With ws_rpt
    If ws_raw.Range("H2") <> "" Then
        i = Application.CountIf(.Range("S2:CCC2"), 5)
        ws_raw.Range("I2").Value = i

        i = Application.CountIf(.Range("S2:CCC2"), 6)
        ws_raw.Range("J2").Value = i

        i = Application.CountIf(.Range("S2:CCC2"), 7)
        ws_raw.Range("K2").Value = i

        i = Application.CountIf(.Range("S2:CCC2"), 8)
        ws_raw.Range("L2").Value = i
    Else

    End If
End With

Jeremy · Accepted Answer · 2016-09-14 14:40:26Z

1

You've the numbers you're counting converted to text by putting them in double-quotation marks - try this:

i = WorksheetFunction.CountIf(Range("S2:CCC2"), 5)
ws_raw.Range("I2").Value = i


i = WorksheetFunction.CountIf(Range("S2:CCC2"), 6)
ws_raw.Range("J2").Value = i


i = WorksheetFunction.CountIf(Range("S2:CCC2"), 7)
ws_raw.Range("K2").Value = i


i = WorksheetFunction.CountIf(Range("S2:CCC2"), 8)
ws_raw.Range("L2").Value = i

answered Sep 14, 2016 at 14:40

Jeremy

1,3373 gold badges13 silver badges28 bronze badges

Collectives™ on Stack Overflow

Count if function not working properly in vba

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related