Приложение Excel.WorksheetFunction.CountПри использовании относительных ссылок

Я постараюсь сделать это максимально простым и неконкретным. У меня есть три столбца, a, b, c. Col C имеет сто случайных чисел в ячейках C1-C100. Col A имеет пять случайных чисел в ячейках C1: C5.

Для каждой ячейки B1:B5 я хочу ввести, сколько раз число в столбце a появляется в столбце c. Если бы вы записывали формулу непосредственно в ячейку B1, это было бы так:

=СЧЁТЕСЛИ($C:$C, $A1)

Затем вы должны перетащить вниз, чтобы заполнить ячейки B2: B5

Я хочу сделать это с помощью функции VBA Application.WorksheetFunction.CountIf (мой фактический проект намного сложнее этого, и ячейки должны быть заполнены автоматически).

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

Это работает правильно. Но мне интересно, можно ли это сделать без цикла For-Each и без указания, сколько ячеек в B должно быть заполнено (распознавая, когда A достигает своего последнего значения, и перестает заполнять B соответственно, а-ля

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

На мой взгляд, ключевой строкой было бы что-то вроде:

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

Но это не работает. Все, о чем я прошу, возможно? Я предполагаю, что позволить Excel сразу заполнить весь диапазон будет быстрее, чем зацикливание.


excel countif vba
person Swiftslide    schedule 08.05.2012    source источник

Ответы (2)


arrow_upward
1
arrow_downward

Вы можете использовать оценку:

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
person Charles Williams    schedule 08.05.2012

arrow_upward
1
arrow_downward

Вам не нужен цикл и вам не нужно использовать функцию рабочего листа :) Это сделает то, что вы хотите.

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

ПРИМЕР

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
person Siddharth Rout    schedule 08.05.2012
comment
Будет, и я обдумал это. Но мне нужно написать целую кучу формул, некоторые из них чрезвычайно сложны, и у меня нет терпения для построения строк с использованием переменных vb. Выше приведен простой пример. Реальность сложнее. - person Swiftslide; 09.05.2012
comment
Можете ли вы показать мне пример сложной формулы, которую вы планируете использовать? - person Siddharth Rout; 09.05.2012