格納したデータを配列のように扱う方法はありますか？

Excel VBAの処理で困っています。

いくつかのシートがあり、そのうちの特定の部分をData1, Data2, Data3のように格納しています。
例：Data1 = range(Sheets("田中").cells(11,1),Sheets("田中").cells(51,21))

この格納したData1, Data2, Data3...を配列のように扱うことはできるのでしょうか？

例えば
if i = 1 then j = Data(i)(1,1) のようなふうに扱いたいのです。

試しにData = "Data" & iとしてData1からデータを取り出そうと思いましたが、当然ですが、無理でした。

何かいい方法があればよろしくお願いいたします。

No.4 ベストアンサー 回答者： fujillin 回答日時： 2023/06/05 10:15

こんにちは

ご提示の例からは、値を保持したいのかRangeを保持したいのかがはっきりしませんけれど・・

Data(i)で参照したいのなら、その様に定義しておけば良いでしょう。
　Dim Data(1 to 3)
としておいて、
　Data(1) = Range( ～～～ ).Value
　Data(2) = Range( ～～～ ).Value
　　　・・・・・
あるいは
　Set Data(1) = Range( ～～～ )
のように配列として定義しておけば可能です。


現状のままで行いたいのなら、
　Dim Data
　Data = Array(Data1, Data2, Data3)
のような形式にしておいても可能です。
（インデックスを0始まりにしていると添字がずれますが、Option Base 1 としておけば1、2・・になります）

この回答へのお礼

回答ありがとうございます。

Data(1) = Range( ～～～ ).Valueのように配列に特定の範囲のデータを格納できるのですね。この方法でやってみたいと思います。

お礼日時：2023/06/05 16:47

No.8 回答者： rexfan 回答日時： 2023/06/05 13:19

はい。

Data1,2,3という名前をすでに使ってなければ
その形で格納しておいて、使う時に Data(i)からDatax（Data1のレイアウト）に入れてやれば望んだカウンター処理ができますね。

No.7 回答者： Qchan1962 回答日時： 2023/06/05 12:36

こんにちは

横から失礼します
方法はすでに回答にあるような方法などありますが　
難しく考えすぎのような気がいます（余計なお世話かも知れません）

ご存知の通りSheetsはコレクションなので繰り返し処理や単一呼び出しが出来ます
オブジェクト数などから推察して処理スピードも気になるような悪化はないのではないでしょうか・・・

https://oshiete.goo.ne.jp/qa/13489174.html　も拝見しました
コード内に Array("田中","本田","南","上田",...)としてしまうとメンテナンスの時に困る可能性もありますし　Sheets(MyArray(i))でエラーが発生する可能性もあります

お望みのロジックで　Set ws(i) = Worksheets(arr(i))　が必要かどうかです
単純に
該当シート全てに同じ処理をするのなら
Dim ws As Worksheet
Dim personal_name As Variant
Dim i As Long
With Worksheets(1)
personal_name = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
For Each ws In Worksheets
For i = 1 To UBound(personal_name)
If ws.Name = personal_name(i, 1) Then
'True メイン処理
Exit For 'シート名は一意の為照合ループから抜ける
End If
Next i
Next ws
こんな処理でも良いと思います

各シートの範囲をシート別に配列に入れる場合は、構造を検討する必要がありますね　構造体にする方法やあらかじめ大きさを取得して3次元配列にするとかでしょうが、あまりメリットを個人的には感じないです
（すでにオブジェクト構造になっているしね）

Dim ws As Worksheet
Dim personal_name As Variant
Dim i As Long
With Worksheets(1)
'シートインデックス1のA列最終行までの値を配列に入れている（シート名キーとして）
personal_name = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With
For i = 1 To UBound(personal_name)
Select Case personal_name(i, 1)
Case "田中"
With Worksheets(personal_name(i, 1))
Dim 田中 As Variant
'シート田中への処理
田中 = .Range(.Cells(1, 1), .Cells(Rows.Count, 10).End(xlUp))
End With
Case "本田"
Case "南"
Case "上田"
Case Else
End Select
Next i
Sheets(1).Range("D1").Resize(UBound(田中, 1), UBound(田中, 2)) = 田中

ここで止めてシート毎の範囲を個々の配列に入れる方が判り易いですし、呼び出しも分岐処理やサブルーチンなどで書けばそんなに制作コストはかからないと思います
（むしろ複雑にするとデバッグやメンテナンスにコストがかかると思います）

もっとも　配列田中などを更にまとめるのであれば　シート範囲からのVariant配列のインデックスは１からなので　初めから

Dim Data(1 To 配列数, 1 To 最大行数, 1 To 最大列数)　で　
例えば（10行2列の共通範囲　静的配列）として

Dim personal_name As Variant
With Worksheets(1)
personal_name = .Range("A1", .Cells(Rows.Count, 1).End(xlUp))
End With

ReDim Data(1 To UBound(personal_name), 1 To 10, 1 To 2)
Dim n As Long, i As Long, j As Long
Dim Rng As Range
For n = 1 To UBound(personal_name)
With Worksheets(personal_name(n, 1))
Set Rng = .Range("A1", .Cells(Rows.Count, 2).End(xlUp))
For i = 1 To Rng.Rows.Count
For j = 1 To Rng.Columns.Count
Data(n, i, j) = Rng.Item(i, j)
Next j
Next i
End With
Next n

Dim x As Long
x = 1
With Worksheets(1)
For n = 1 To UBound(Data, 1)
For i = 1 To UBound(Data, 2)
For j = 1 To UBound(Data, 3)
.Cells(x, j + 4) = Data(n, i, j)　'D列に出力
Next j
x = x + 1
Next i
Next n
End With
End Sub

試験的なご質問かもしれませんが動的配列や出力方法、出力インデックスなどなど面倒かも知れませんね、多次元にする必要があるのかなと思いました

No.6 回答者： tatsumaru77 回答日時： 2023/06/05 10:58

No5です。

文字数オーバーなので２回にわけました。
実行結果です。
x= 0 i= 1 j= 1 data= 田中A1
x= 0 i= 1 j= 2 data= 田中B1
x= 0 i= 1 j= 3 data= 田中C1
x= 0 i= 1 j= 4 data= 田中D1
x= 0 i= 1 j= 5 data= 田中E1
x= 0 i= 2 j= 1 data= 田中A2
x= 0 i= 2 j= 2 data= 田中B2
x= 0 i= 2 j= 3 data= 田中C2
x= 0 i= 2 j= 4 data= 田中D2
x= 0 i= 2 j= 5 data= 田中E2
x= 0 i= 3 j= 1 data= 田中A3
x= 0 i= 3 j= 2 data= 田中B3
x= 0 i= 3 j= 3 data= 田中C3
x= 0 i= 3 j= 4 data= 田中D3
x= 0 i= 3 j= 5 data= 田中E3
x= 0 i= 4 j= 1 data= 田中A4
x= 0 i= 4 j= 2 data= 田中B4
x= 0 i= 4 j= 3 data= 田中C4
x= 0 i= 4 j= 4 data= 田中D4
x= 0 i= 4 j= 5 data= 田中E4
x= 0 i= 5 j= 1 data= 田中A5
x= 0 i= 5 j= 2 data= 田中B5
x= 0 i= 5 j= 3 data= 田中C5
x= 0 i= 5 j= 4 data= 田中D5
x= 0 i= 5 j= 5 data= 田中E5
x= 1 i= 1 j= 1 data= 本田A1
x= 1 i= 1 j= 2 data= 本田B1
x= 1 i= 1 j= 3 data= 本田C1
x= 1 i= 1 j= 4 data= 本田D1
x= 1 i= 1 j= 5 data= 本田E1
x= 1 i= 2 j= 1 data= 本田A2
x= 1 i= 2 j= 2 data= 本田B2
x= 1 i= 2 j= 3 data= 本田C2
x= 1 i= 2 j= 4 data= 本田D2
x= 1 i= 2 j= 5 data= 本田E2
x= 1 i= 3 j= 1 data= 本田A3
x= 1 i= 3 j= 2 data= 本田B3
x= 1 i= 3 j= 3 data= 本田C3
x= 1 i= 3 j= 4 data= 本田D3
x= 1 i= 3 j= 5 data= 本田E3
x= 1 i= 4 j= 1 data= 本田A4
x= 1 i= 4 j= 2 data= 本田B4
x= 1 i= 4 j= 3 data= 本田C4
x= 1 i= 4 j= 4 data= 本田D4
x= 1 i= 4 j= 5 data= 本田E4
x= 1 i= 5 j= 1 data= 本田A5
x= 1 i= 5 j= 2 data= 本田B5
x= 1 i= 5 j= 3 data= 本田C5
x= 1 i= 5 j= 4 data= 本田D5
x= 1 i= 5 j= 5 data= 本田E5
x= 2 i= 1 j= 1 data= 南A1
x= 2 i= 1 j= 2 data= 南B1
x= 2 i= 1 j= 3 data= 南C1
x= 2 i= 1 j= 4 data= 南D1
x= 2 i= 1 j= 5 data= 南E1
x= 2 i= 2 j= 1 data= 南A2
x= 2 i= 2 j= 2 data= 南B2
x= 2 i= 2 j= 3 data= 南C2
x= 2 i= 2 j= 4 data= 南D2
x= 2 i= 2 j= 5 data= 南E2
x= 2 i= 3 j= 1 data= 南A3
x= 2 i= 3 j= 2 data= 南B3
x= 2 i= 3 j= 3 data= 南C3
x= 2 i= 3 j= 4 data= 南D3
x= 2 i= 3 j= 5 data= 南E3
x= 2 i= 4 j= 1 data= 南A4
x= 2 i= 4 j= 2 data= 南B4
x= 2 i= 4 j= 3 data= 南C4
x= 2 i= 4 j= 4 data= 南D4
x= 2 i= 4 j= 5 data= 南E4
x= 2 i= 5 j= 1 data= 南A5
x= 2 i= 5 j= 2 data= 南B5
x= 2 i= 5 j= 3 data= 南C5
x= 2 i= 5 j= 4 data= 南D5
x= 2 i= 5 j= 5 data= 南E5

No.5 回答者： tatsumaru77 回答日時： 2023/06/05 10:52

1例です。

田中、本田、南の各シートのA11：E15の内容が
添付図のようになっていたとします。
この内容を、イミデイトウインドウに表示します。
Public Sub 配列の配列試験()
Dim data(2) As Variant
Dim i As Long
Dim j As Long
Dim x As Long
data(0) = Range(Worksheets("田中").Cells(11, 1), Worksheets("田中").Cells(15, 5)).Value
data(1) = Range(Worksheets("本田").Cells(11, 1), Worksheets("本田").Cells(15, 5)).Value
data(2) = Range(Worksheets("南").Cells(11, 1), Worksheets("南").Cells(15, 5)).Value
For x = 0 To 2
For i = 1 To UBound(data(x), 1)
For j = 1 To UBound(data(x), 2)
Debug.Print "x=", x, "i=", i, "j=", j, "data=", data(x)(i, j)
Next
Next
Next
End Sub

No.3 回答者： 初心者ですが何か。 回答日時： 2023/06/05 09:56

邪道な昭和ジジィなので、

Sub try()
Dim i As Integer
Dim Data1 As Range, Data2 As Range, Data3 As Range
Dim Data

Set Data1 = Range(Sheets(1).Cells(2, 2), Sheets(1).Cells(4, 5))
Set Data2 = Range(Sheets(2).Cells(3, 4), Sheets(2).Cells(6, 7))
Set Data3 = Range(Sheets(3).Cells(4, 3), Sheets(3).Cells(8, 9))

Data = Array(Data1, Data2, Data3)

For i = LBound(Data) To UBound(Data)
Debug.Print Data(i).Cells(1, 1).Address
Next

End Sub

結果：
$B$2
$D$3
$C$4

各シート名はインデックス番号使う事で手抜きしました。
セル範囲はそれぞれで違いますので、.Cells(1,1) にあたるセルアドレスは変わってきます。

勘違いでしたらごめんなさい。

この回答へのお礼

回答ありがとうございます。

＞Set Data1 = Range(Sheets(1).Cells(2, 2), Sheets(1).Cells(4, 5))
できればこの部分の入力を楽できるようにしたいのです。
シート名が変わることもあり、削除と作成を繰り返したため(？)、インデックス番号も続き番号にはなっていませんので。

お礼日時：2023/06/05 17:00

No.2 回答者： neKo_deux 回答日時： 2023/06/05 09:47

変数名の数字で処理するってのは無理。

というか、そんな事されたらうかつに数字の変数名使えなくて困る。

最初から配列として宣言しておくか。
変数を一旦配列に格納、処理した後で元の変数に戻すとか。
が一般的な対応だと思います。

この回答へのお礼

回答ありがとうございます。

はい、私も変数名の数字で処理するのは無理があると思っていました。配列にシートや特定範囲のデータを入れることができるようなので、配列を使っていこうと思います。

お礼日時：2023/06/05 17:00

No.1 回答者： rexfan 回答日時： 2023/06/05 09:39

Data1, Data2, Data3を紐づけるために上位の配列を作って格納し

そこからData(i)としてひとつづつ取り出して使うのが簡単かと。

この回答へのお礼

忙しい中、回答ありがとうございます。

＞上位の配列を作って格納
これは最初にData(10)とか宣言しておいて(動的配列でもいいですが)、Data(i) = range(Sheets("田中").cells(11,1),Sheets("田中").cells(51,21))のようにするということでしょうか？

お礼日時：2023/06/05 09:54