CODEWITHSUNDEEP
vbaexcel
Ben
Ben

Reputation: 83

Making "Countif" worksheet function variable

I have a code that counts the cell in a range if there's a number. The total number of cell counted will then be shown in row 3. My issue now is that the formula is not variable and I have no idea how to make it too. If I enter this code, row 3 reports all the same result (which is from the first column of data). Hoping somebody can help me here!!

Sub Six_Continue()

    Dim Rng As Range
    Dim LastClm As Long


    With ActiveSheet
        LastClm = .Cells(1, .Columns.Count).End(xlToLeft).Column
        Set Rng = Range(.Cells(3, 3), .Cells(3, LastClm))
        Rng.Value = Application.WorksheetFunction.CountIf(Range("C5", "C" & Cells(Rows.Count, 5).End(xlUp).Row), "<>?")
    End With

End Sub

Upvotes: 2

Views: 692

Answers (2)

dadler
dadler

Reputation: 145

Your CountIf will count cells even if they don't contain a number. Using Count ensures that only cells containing numbers are taken into account.

Sub Six_Continue()

    Dim Rng As Range
    Dim LastClm As Long
    Dim myClm As Long


    With ActiveSheet
        LastClm = .Cells(1, .Columns.Count).End(xlToLeft).Column
        For myClm = 1 To LastClm
            Set Rng = .Cells(3, myClm)
            Rng.Value = Application.WorksheetFunction.Count(Range(.Cells(5, myClm), .Cells(.Cells(Rows.Count, myClm).End(xlUp).Row, myClm)))
        Next myClm
    End With

End Sub

Upvotes: 2

Michał Turczyn
Michał Turczyn

Reputation: 37500

Try this modified version:

Sub Six_Continue()

Dim Rng As Range
Dim LastClm As Long, i As Long
LastClm = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To LastClm
    Cells(3, i).Value = Application.WorksheetFunction.CountIf(Range(Cells(1, i), Cells(2, i)), "<>?")
Next
End Sub

Upvotes: 0

Related Questions