Excel、VBAで複数の範囲を格納したrangeオブジェクトを、配列のように操作したい

Question

前提・実現したいこと

Excel, VBAにおいて、
離れた複数の範囲を取得し、それらのみを配列のように　※array(1,3), array(24,100)など
操作（取得・反映）したい

該当のソースコード

Dim hoge As range
Set hoge = ThisWorkbook.Worksheets("Sheet1").range("A1:A3,C1:C3")
hoge = 123
hoge(1, 2) = 55
hoge(1, 4) = 55

試したこと

rangeオブジェクトで、値の設定（一括のみ）は出来たが、
値の設定（単一セル）と、値の取得が行えない

hoge=123　でA1A3, C1C3に全て「123」が反映されるが、
hoge(1,2)　でC1に反映されず、B1に「55」が反映されてしまう
また、hoge(1,4)など明らかな範囲外にも設定できる

目的

VBAのFindで一致したものから、Offsetで値反映を行いたい為
A1A1000がキー列
E1E1000が取得したい値の場合、

A列HITで、offset(1,5)と取得しなければならないが、
A列とE列のみを引っ張り、offset(1,2)のように取得したいため

補足情報（FW/ツールのバージョンなど）

Excel2013

Answer 1

Rangeは配列ではないですからね。
hoge(1, 2) = 55は、実際はhoge.Cells.Item(1, 2) = 55です。（.Cells.Itemは既定のプロパティなので省略できる）
Range.Item プロパティ (Excel) | Microsoft Docs
上記のヘルプに「返される範囲の最初の領域の左上のセルへの相対的な1ベースのオフセットです。」との記述があります。

極力シンプルに記述するなら下記のような感じでしょうか。

    Dim hoge As Range
    Set hoge = ThisWorkbook.Worksheets("Sheet1").Range("A1:A3,C1:C3")

    hoge.Value = 123
    hoge.Areas(2)(1) = 55 '2つ目のエリア(C1:C3)の最初のセル(C1)

A列HITで、offset(1,5)と取得しなければならないが、

A列とE列のみを引っ張り、offset(1,2)のように取得したいため

2だろうが5だろうがマジックナンバーには変わりないので、
可読性を重視したいということなら、私なら下記のような実装にするかな。

    With ThisWorkbook.Worksheets("Sheet1")
        Dim keyRng As Range:   Set keyRng = .Range("A1:A1000")
        Dim valueRng As Range: Set valueRng = .Range("E1:E1000")
    End With
    
    Dim FoundCell As Range
    Set FoundCell = keyRng.Find(What:="hoge")
    
    valueRng(FoundCell.Row) = "HIT"

Comments

[funmas]

いつも回答ありがとうございます！
プロパティが省略されているとは知りませんでした。
左上基点のため今回のような処理が行えたわけですね。

Areasプロパティは一度発見したのですが、
そのような記述方法を見つけられず、目的の用途と違うものかと見逃していました。
まさかカッコを連続で使用するとは・・・(配列のようにカンマ区切りで書いてみていた）

やりたいことは教えていただいたAreasで見事に実現できました
大変助かりました、ありがとうございます！

PS.範囲を2列以上まとめて設定する運用はしないため問題なさそうです

[funmas]

追加の回答ありがとうございます。
Withでそこまでキレイに書けるとは驚きです。ぜひ参考にさせていただきます。

Rowでの取得に関しては、一度挑戦したのですが、
参照範囲が1行目から始まっていないと取り回しが難しいようで、
A5セルがHITし、B2～B1000の範囲に、Rowで値を代入すると、
B6セルに代入されてしまうことが問題でしたが、
ここに関しては、開始行でズレた分、.Rowから差し引いて代入しようかと思います

キレイなコードですので、一度こちらで作成してみたいと思います。
ありがとうございました！

[hatena19]

valueRng(FoundCell.Row - valueRng(1).Row + 1) = "HIT"
とするかな。うーん、あまりスマートでないですね。
結局下記のような感じが一番シンプルかな。
Const valueOffset = 5
・・・
FoundCell.Offset(, valueOffset) = "HIT"

[funmas]

いえ！
valueRng(FoundCell.Row - valueRng(1).Row + 1) = "HIT"
こちら個人的にかなりスマートです。
Rowを手入力で確保しなければと考えていましたが、
valueRng(1).Row
このような記述でも取得できるとは・・・！

もとよりクラス化にしてブラックボックスなつくりで運用する予定でしたので、
むしろこの記述方はありがたいです。
これで運用したいと思います！ありがとうございます！

[imihito]

横から失礼します。

私の場合、見つかったセルをEntireRowで行全体にしてからIntersectで取得することが多いです(ちょっと大仰な感じはありますが)
Intersect(FoundCell.EntireRow, valueRng).Value = "HIT"

Answer 2

解決済みのようですが。。。

Sub test()
    Dim rng As Range
    Dim vv(1 To 2) As Variant
    Dim ix As Long
    
    Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:A30,E1:E30")
    Set vv(1) = rng.Areas(1)
    Set vv(2) = rng.Areas(2)
    
    On Error GoTo WayOut
    ix = WorksheetFunction.Match(123, vv(1), 0)
    On Error GoTo 0
    
    MsgBox vv(2)(ix).Value
    
WayOut:
End Sub

無理やり感が半端ないですね＾＾；
クラスを作るなら、どうにでもなるかと思いますが、、、、
敢えてクラスを自作する必要性があるのかな。。。。

Sub test2()
    Dim Rng As Range
    Dim ixRow As Variant
    Dim sProm As String
    
    Set Rng = ThisWorkbook.Worksheets("Sheet1").Range("A1:E30")
    ixRow = Application.Match(123, Rng.Columns(1), 0)
    
    If IsError(ixRow) Then
        sProm = "見つかりませんでした。"
    Else
        With Rng(ixRow, 5)
            Application.Goto .Cells
            sProm = .Address(False, False)
        End With
    End With
        
    MsgBox sProm
End Sub