マクロ 関数式の中にRangeでセルを参照するには

質問

Range("B2").Formula = "=COUNTIF($A$2:$A$100,A2)"

A行のセル（１列はタイトルなのでA2以降）
に入力させているものが
A行自身の中に何個あるかを数えて
B２以降に表示させたいのですが
A列の最終行を取得して（下の式で）
↑の式の＄A$100の部分に
どのように記述したらよろしいでしょうか？
もしくは　考える方向がおかしい、というような
御指摘もありましたらお願いします
（B列には現時点では式のまま表示しても
　問題ありません。
　B列の式を下方向へコピーする方法は
　以前こちらで御教授していただきました）

Dim mRng As Range
Set mRng = Range("A2", Range("A65536").End(xlUp))

回答No.2

imogasi · ベストアンサー

エクセルVBAで関数を使うには
（１）Range("B2").Formula ="=関数"を使う
（２）WorksheetFunctionを使う
（３）繰り返しのロジックを使う（関数を使うに該当しませんが）
（２）は
Sub test01()
d = Range("A65536").End(xlUp).Row
MsgBox d
Cells(2, "B") = WorksheetFunction.CountIf(Range(Cells(2, "A"), Cells(d, "A")), Cells(1, "A"))
End Sub
（１）は
Sub test02()
d = Range("A65536").End(xlUp).Row
MsgBox d
x = "A" & d
s = "=CountIf(A2:" & x & ",A1)"
MsgBox s
Cells(2, "B").Formula = s
End Sub
なんてのも出来ます。
（３）は
Sub test03()
d = Range("A65536").End(xlUp).Row
MsgBox d
c = 0
For i = 2 To d
If Cells(i, "A") = Cells(1, "A") Then
c = c + 1
End If
Next i
Cells(2, "B") = c
End Sub
のようなものです。

お礼

遅くなって申し訳ありません
様々な角度からの回答ありがとうございます

同じ結果を出すにも
いろいろ方法があるのですね
まだ使い分けるまでいたりませんが
地道に勉強したいです

回答No.3

ja7awu

Range("B2").Formula = "=COUNTIF($A$2:" & Range("A65536").End(xlUp).Address & ",A2)"

で如何でしょうか。　

A列のデータがある最終行まで、B列に計算式を設定するのであれば、

Dim Rng As Range
For Each Rng In Range("A2", Range("A65536").End(xlUp))
　　Rng.Offset(, 1).Formula = "=COUNTIF($A$2:" & _
　　Range("A65536").End(xlUp).Address & "," & Rng.Address(False, False) & ")"
Next Rng

たぶん、このようなことだと思いますが、違っていたらごめんなさい。

お礼

遅くなって申し訳ないです
式のほかに式コピーまで記入していただいて
ありがとうございました。

しかし、addressとrangeの
使い分けというか微妙な記述の違いって
難しそうですね＾＾
ありがとうございました

回答No.1

mshr1962

Range("B2").Formula = "=COUNTIF($A$2:$A$" & Range("A65536").End(xlUp).Row & ",A2)"

で出来ませんか？

お礼

遅くなって申し訳ないです
無事できました　ありがとうございました！