Excel VBA For Each Next構文内の別シートを対象とする方法

こんにちは。
ＶＢＡ初心者のものですが教えてください。

「sheet1のC29:U29とsheet2のC31:G31について
1より小さければ小数第2位まで表示する」
の構文を作成したいのですが、
下記の構文ではエラーが出てしまいました。
どのように訂正すればよいでしょうか？
※できればrangeプロパティを使いたいのですが、
　cellsプロパティを使わなきゃできませんか？

すみませんがご教示をお願いいたします。

Sub test()

Dim myrange As Range
For Each myrange In Worksheets("sheet1").Range("C29:U29"),Worksheets("sheet2").Range("C31:G31")
If myrange.Value < 1 Then
myrange.NumberFormatLocal = "0.00"
End If
Next myrange



End Sub

No.4 ベストアンサー 回答者： Wendy02 回答日時： 2009/09/20 21:52

こんばんは。

一般的に、同種のオブジェクトを確保する場合は、Collection に格納します。ただし、c にエラー値が入っていないことが条件です。

'-------------------------------------------
Sub RngPlus()
　　Dim colRng As Collection
　　Dim rng As Range
　　Dim c As Range
　　
　　Set colRng = New Collection
　　
　　colRng.Add Worksheets("Sheet1").Range("C29:U29")
　　colRng.Add Worksheets("Sheet2").Range("C31:G31")
　　
　　For Each rng In colRng
　　　　For Each c In rng
　　　　　　If c.Value < 1 Then
　　　　　　　　c.NumberFormatLocal = "0.00"
　　　　　　End If
　　　　Next c
　　Next rng
End Sub

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
ご回答ありがとうございました。

設定範囲のシートはもっと増える予定ですので、
ご紹介いただいた方法を今後活用したいと思います。

お礼日時：2009/10/03 11:07

No.6 回答者： Wendy02 回答日時： 2009/09/21 10:41

#4の回答者です。

回答した後に考え直してみたけれども、テクニックとしてはいくつか存在はしていても、できなければ、同じものを二つ書くか、サブルーチンにすればよいのではないでしょうか。私の#4に書いた方法などは、使う必要がないと思います。

>If myrange.Value < 1 Then
>myrange.NumberFormatLocal = "0.00"

それと、1　以下なら、書式を0.00と換えるというのは意味がないのでは？
1以下は、0.1もありますが、-1もTrue が返ります。
たとえば、小数点がある場合とかにしたら良いと思います。

'-------------------------------------------

Sub Main()
Test Worksheets("sheet1").Range("C29:U29")
Test Worksheets("sheet2").Range("C31:G31")

End Sub
Sub Test(rng)
　　Dim myRange As Range
　　For Each myRange In rng
　　　　If myRange.Value - Int(myRange.Value) <> 0 Then
　　　　　　myRange.NumberFormatLocal = "0.00"
　　　　End If
　　Next myRange
End Sub

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
何度もご回答ありがとうございました。

設定範囲のシートは2つだけではないので、
できれば分ける方法ではなく
手軽に増やせる方法が適していると考えています。

また、本件ではマイナス値は取り扱わない状況なので、
INT関数は考慮しなくても大丈夫です。

＃４でご紹介いただいた方法を活用したいと存じます。

お礼日時：2009/10/03 11:12

No.5 回答者： hotosys 回答日時： 2009/09/20 22:44

手っ取り早くarrayで外側にもう一つループを作る場合です。

Sub test()
Dim rng As Variant
Dim myrange As Range
For Each rng In Array(Worksheets("sheet1").Range("C29:U29"), Worksheets("sheet2").Range("C31:G31"))
For Each myrange In rng
If myrange.Value < 1 Then
myrange.NumberFormatLocal = "0.00"
End If
Next myrange
Next rng
End Sub

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
ご回答ありがとうございました。

設定範囲のシートはもっと増える予定ですので、
ご紹介いただいた方法を今後活用したいと思います。

お礼日時：2009/10/03 11:08

No.3 回答者： cistronezk 回答日時： 2009/09/20 20:56

＞For Each myrange In Worksheets("sheet1").Range("C29:U29"),Worksheets("sheet2").Range("C31:G31")

こういう書き方は出来ません。別シートですからUnionも使えませんね。「Worksheets("sheet1").Range("C29:U29")」と「Worksheets("sheet2").Range("C31:G31")」との２回に分けるのが素直なやり方です。

実は設定範囲がもっとたくさんある、というのなら、
・Rangeを配列に格納する
・桁設定をサブルーチンにしておく
ことで、For文で回してはどうでしょう。

Sub test()
Dim rs(0 To 1) As Range, i As Integer
Set rs(0) = Worksheets("sheet1").Range("C29:U29")
Set rs(1) = Worksheets("sheet2").Range("C31:G31")
For i = 0 To UBound(rs)
ケタ設定 rs(i)
Next
End Sub

Sub ケタ設定(r As Range)
Dim c As Range
For Each c In r
If c.Value < 1 Then
c.NumberFormatLocal = "0.00"
End If
Next
End Sub

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
ご回答ありがとうございました。

ご指摘の通り、設定範囲はもっと増える予定ですので、
ご紹介いただいた方法を今後活用したいと思います。

お礼日時：2009/10/03 11:03

No.2 回答者： imogasi 回答日時： 2009/09/20 20:49

同一シートなら

Sub test()
Dim myrange As Range
Set x = Union(Worksheets("sheet1").Range("A1:B2"), Worksheets("sheet1").Range("D1:E3"))
For Each myrange In x
MsgBox myrange
Next myrange
End Sub
ができる。
ーー
しかし
http://support.microsoft.com/kb/291308/ja
の１５：２2 つ以上の指定した範囲を選択する方法
にあるように、「Union メソッドはシートを越えて使用することはできません。」のです。
Sub test()
Dim myrange As Range
Set x = Union(Worksheets("sheet1").Range("A1:B2"), Worksheets("sheet2").Range("D1:E3"))
For Each myrange In x
MsgBox myrange
Next myrange
End Sub
はエラー。
Sub test()
Dim myrange As Range
Set x = Union(Worksheets("sheet1").Range("A1:B2"), Range(sh2range))
For Each myrange In x
MsgBox myrange
Next myrange
End Sub
もエラー。
Range(sh2range)のsh2rangeは範囲名定義。
ーーー
シートごとの範囲にＦｏｒ　Ｅａｃｈ　Ｎｅｘｔを使うほか無いと思う。
Ｕｎｉｏｎメソッドは初心者には超えた課題かと思う。

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
ご回答ありがとうございました。
やはりシートごとに分けるのが一番無難な方法ですね。

お礼日時：2009/10/03 11:00

No.1 回答者： xls88 回答日時： 2009/09/20 20:14

sheet1とsheet2のFor Each文を分けて記述してみてください。

この回答へのお礼

お礼が遅くなりまして申し訳ありません。
ご回答ありがとうございました。
入門レベルには一番優しい方法ですね。

お礼日時：2009/10/03 10:59