非アクティブシートでの For Each Next での操作について

エクセルVBAで何度もお世話になってます。目的のものほぼ完成いたしましたが，少しずつ改良してま

す。

いろいろ検索してますと，For　Next　の処理よりも，For　Each　Nextの処理が早いとあります。

そこで，For　Each　Nextを使ったものを他サイトを参考に，下記のように作成しました。

Dim Kensaku_hani As Range
Dim colRng As Collection

Set colRng = New Collection
colRng.Add Worksheets("処理記録ｙ").Range("G2:G10")

For Each Kensaku_hani In colRng

MsgBox Worksheets("処理記録").Cells(Kensaku_hani.Row, Retu).Value

Next

動かしてみると，ループが一回シカ回りません。それと　 Kensaku_hani.Value　も取得できません。

シートをアクティブにしてしまえば，Collctioon　を使わずともできるのですが，Collectionを

使うとうまくいきません。アドバイスをお願いできればと思います。実際の運用では，

MsgBoxのところで，検索，転記を行います。

No.1 回答者： めぐみん_ 回答日時： 2021/02/22 19:49

普通

Dim Kensaku_hani As Range
Dim colRng As Range

Set colRng = Worksheets("処理記録ｙ").Range("G2:G10")

For Each Kensaku_hani In colRng

MsgBox Worksheets("処理記録").Cells(Kensaku_hani.Row, Retu).Value

Next

ともにRangeにするとかでは？（Retu ってなに？）

No.2 回答者： osamuy 回答日時： 2021/02/22 20:20

＞colRng.Add Worksheets("処理記録ｙ").Range("G2:G10")

上記文は、コレクションにRangeオブジェクトを1個追加しただけなので、ループは1回しか回らないし、Kensaku_haniはRangeオブジェクトです。
colRngをウォッチすれば、分かりやすいです。※添付画像参照。

高速化目的なら、Range.GetData()を呼び出して値を配列化する手を使うとか。

この回答へのお礼

アドバイスありがとうございました。

あるサイトに載っていたもの疑わずに使いました。

いろいろくぐってみると，要素を追加する必要があるようで，

勉強になりました。

下記のように，要素を追加したら，うまく行きました。

For　ｋ＝２　To　最終行
　colRing.Add WorkSheets("処理記録").Cellｓ（k,5）
Next

これでいいのかどうか解りませんが。

それから，Range.GetData()　を使うと高速化がはかれるとの

ことですが，どのようにしたらいいのかはまだ，解りません。

いろいろ勉強になるアドバイスありがとうございました。

お礼日時：2021/02/23 19:52

No.3 回答者： Qchan1962 回答日時： 2021/02/22 20:32

こんばんは、

New Collection
colRng.Add　の内容（意味）item.keyをご確認ください。
For Each Kensaku_hani In colRng.Item(1)
If Not Kensaku_hani Is Nothing Then
MsgBox Worksheets("処理記録").Cells(Kensaku_hani.Row, Retu).Value
Next
違っていたら、ごめんなさい。

この回答へのお礼

アドバイスありがとうございます。

＞colRng.Add　の内容（意味）

最初ここがよく分かりませんでしたが，

ネットでくぐってみて，やっと意味が分かりました。

範囲を入れれば，範囲の間のものが自動で入る，みたいに

思っておりました。

アドバイスありがとうございました。

お礼日時：2021/02/23 20:02

No.4 ベストアンサー 回答者： めぐみん_ 回答日時： 2021/02/22 20:33

No.1です。

Collection ではなく Dictionaryオブジェクトなら、

Dim myDic As Object
Dim r As Range

Set myDic = CreateObject("Scripting.Dictionary")

Set myDic("範囲") = Worksheets("Sheet1").Range("A1:A5")

For Each r In myDic("範囲")

Debug.Print r.Value

Next

Set myDic = Nothing

こんな感じでも行けましたけど？

No.5 回答者： めぐみん_ 回答日時： 2021/02/23 08:09

初級者の検証なので間違いは確実かもですが。

CollectionってSetステートメントが使えないのではないかなと。
いくつか試しましたがエラーになってしまいました。

この回答へのお礼

検証ありがとうございます。

最初の質問のマクロに，抜けていた部分があり，

Dim　colRing　As　Collection
Set　colRing=New　Collection

みたいにするみたいでした。

何度もありがとうございました。

お礼日時：2021/02/23 19:59

No.6 回答者： めぐみん_ 回答日時： 2021/02/23 20:47

No.5のお礼について。

＞最初の質問のマクロに，抜けていた部分があり，

いえ、抜けてませんよ。

私が試したのはRangeオブジェクトを変数に入れるにはNo.5の

＞Set myDic("範囲") = Worksheets("Sheet1").Range("A1:A5")

のようにSetステートメントでやらないとダメみたいって感じがしたと言う事です。
Collectionは .Add で値を入れていくみたいなのでSetステートメントを使おうとしてもエラーばかりでした。
単純に方法を見つけられないだけだったのかもしれませんけど。