VBA Cells(1,1)で指定したセルをRange("A1")に変換し、その範囲をCountAしたい。

Question

お疲れ様です。

下記コードをご覧いただけますでしょうか。

Sub HogeMethd()
　　Dim S11 As WorkSheet
    sheet11.Activate
    Set S11.ActiveSheet
    
    Dim Idx As Integer
    Dim CountResult As Integer
    Dim Range1 As String
    Dim Range2 As String

    For iIdx = 1 To 500
        Range1 = Cells(iIdx + 4, 17).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        Range2 = Cells(iIdx + 4, 117).Address(RowAbsolute:=False, ColumnAbsolute:=False)
        'CountResult = Application.WorksheetFunction.CountA(S11.Range("""Range1:Range2"""))
        'Debug.Print (CountResult)
        Debug.Print (Range1)
        Debug.Print (Range2)

    Next
End Sub

今のところ、CountResultの行はコメントアウトしていますが、
一回目のループで、
Range1はQ5
Range2はDM5と表示されます。

やりたいのはApplication.WorksheetFunction.CountA(S11.Range("Q5:DM5"))で取得できる数値をCountResultに入れたいのですが、
Range1,Range2はともに変数であるため、
Application.WorksheetFunction.CountA(S11.Range("Range1:Range2"))という書き方ではCountAの結果取得はできないことはわかっています。

そこで"のエスケープについて調べ、S11.Range("""Range1:Range2""")としたのですが、
これでも上手くいかないようでした。

また別途、解決策として以下があげられるのですが、こちらも実装方法がわかりません。
１．Cellsで範囲参照を行いRangeを使用せずCountAする。
よろしくお願いいたします。

追記
質問してから、Range(Cells(iIdx + 4, 17),Cells(iIdx + 4, 117)).Select)で行ける気がしてきたので、
試してみます。

追記２
↑上手くいきませんでした。

Answer 1

＞Range(Cells(iIdx + 4, 17),Cells(iIdx + 4, 117)).Select)で行ける気がしてきた

この記述で「うまくいかなかった」とのことですが、範囲指定の方法としては間違っていないと思います。
部分的な記載ですので実際どのようなコードになっているかにもよりますが、うまくいかない原因は
・RangeやCellsの対象シートが明示されていない
・なぜか.Select)がついている？
といったあたりがあやしそうです。

---

ご自分でこの指定方法にたどりついたのならご存知かもしれませんが、Rangeには２通りの指定方法があります。

①アドレス指定
第１引数（String型）にアドレス文字列を渡すことでセル範囲を取得する方法です。
例：Set rng = Range("Q5:DM5")

②始点セル・終点セル指定
第１引数(Range型)に始点セル、第２引数(Range型)に終点セルを指定して、始点～終点のセル範囲を取得する方法です。
例１：Set rng = Range(Cells(5, 17), Cells(5, 117))
例２：Set rng = Range(Cells(5,"Q"), Cells(5,"DM"))

ちなみにCellsとRangeはどちらもRange型を返しますが、Cellsは単一セルのみを表すのに対し、Rangeは単一セルも範囲セルも表せます。
今回のような始点セル・終点セルなら単一セルでいいのでCellsで指定していますが、Rangeで指定しても問題はありません。
※単一セルを取回す場合にはCellsを使った方が扱いやすい場合が多いです。

---

他の方々からのアドバイスも、このいずれかの方法にするためのものです。
ExcelVBAではここらへんをきっちり押さえておくとスムーズにコードが書けるようになってきますので、面倒でも使用するコマンドをひとつずつ調べて、うまく活用していってください。

Answer 2

Dim Range1 As Range
Dim Range2 As Range

Set Range1 = ...
Set Range2 = ...

CountResult = Application.WorksheetFunction.CountA(S11.Range(Range1,Range2))

ところで、
Set S11.ActiveSheet
ってどういう動きをするんでしょうか？？

Comments

[King_of_Flies]

SET S11 = ActiveSheetの間違いです　すいません。

Answer 3

Range1とRange2は変数なので、そのような使い方はできません。

例えば

Dim Range3 As Range

とあらかじめ定義しておき、

Set Range3 = Range(Cells(iIdx + 4, 17), Cells(iIdx + 4, 117))

で、Range1（Q5）とRange2（DM5）の範囲のオブジェクトを作り、

CountResult = Application.WorksheetFunction.CountA(Range3)

というように、CountAのパラメータとして上記の「範囲のオブジェクト」を送ってやれば、正しい値が得られます。

Comments

[ExcelVBAer]

たぶんお分かりとは思いますが、念の為に補足を。
Range(Cells(), Cells()) の Range,Cells両方の頭に[S11.]を付けないと、
暗黙的にActiveSheetが参照され、
後で処理が変わる等で意図しない結果になる可能性があります（潜在バグ）
のでご注意を。

[cauliflowerjoe]

補足ありがとうございます。
無意識にActiveSheetを使う前提で書いてしまいました。

Answer 4

こんな感じでしょうか（動作未確認）

CountResult = Application.WorksheetFunction.CountA(S11.Range(Range1 & ":" & Range2))