CODEWITHSUNDEEP
excelcountifvba
Swiftslide
Swiftslide

Reputation: 1347

Excel Application.WorksheetFunction.CountIf using relative references

I'll try keep this as simple and nonspecific as I can. I have three columns, a, b, c. Col C has one-hundred random numbers in cells C1-C100. Col A has five random numbers in cells C1:C5.

For each cell B1:B5, I want to enter how many times the number in col a appears in col c. If you were writing the formula directly into cell B1, it would be thus:

=COUNTIF($C:$C, $A1)

You would then drag down to fill cells B2:B5

I want to do this using VBA's Application.WorksheetFunction.CountIf function (my actual project is much more complex than this, and cells need to be filled in automatically). 

Dim colb As Range
Set colb = Range("$B1:$B5")

Dim colc As Range
Set colc = Range("$C:$C")

Dim cola As Range
Set cola = Range("$A:$A")

For Each bcell In colb
    bcell.Formula = Application.WorksheetFunction.CountIf(colc, bcell.Offset(0, -1))
Next bcell

This works correctly. But I'm wondering if it can be done without a For-Each loop, and without specifying how many cells in B are to be filled in, (recognising when A reaches its last value, and stops filling B in accordingly, a la

Dim colb As Range
Set colb = Range("$B:$B")

In my mind the crucial line would be something like:

colb.Formula = Application.WorksheetFunction.CountIf(colc, cola)

But this does not work. Is anything I'm asking possible? I imagine letting Excel fill in the whole range at once would be faster than looping.

Upvotes: 1

Views: 16105

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149315

You don't need the loop nor do you need to use the worksheetfunction :) This will do that what you want.

Range("$B1:$B5").Formula="=COUNTIF($C:$C, $A1)"

EXAMPLE

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim colb As Range

    Set ws = Sheets("Sheet1")
    Set colb = ws.Range("$B1:$B5")

    colb.Formula = "=COUNTIF($C:$C, $A1)"
End Sub

Upvotes: 1

Charles Williams
Charles Williams

Reputation: 23540

You could use evaluate:

Sub testeval()
Dim vArr As Variant
Dim nARows As Long
Dim nCRows As Long
Dim oSht1 As Worksheet
Dim strSheet As String

Set oSht1 = ActiveSheet
strSheet = oSht1.Name & "!"
nARows = oSht1.Range("A1").Offset(oSht1.Rows.Count - 1).End(xlUp).Row
nCRows = oSht1.Range("C1").Offset(oSht1.Rows.Count - 1).End(xlUp).Row
vArr = oSht1.Evaluate("=INDEX(COUNTIF(" & strSheet & "$C1:$C" & CStr(nCRows) & "," & strSheet & "$A1:$A" & CStr(nARows) & "),0,1)")
oSht1.Range("$B1").Resize(UBound(vArr), 1) = vArr
End Sub

Upvotes: 1

Related Questions