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 · ベストアンサー

こんばんは。

一般的に、同種のオブジェクトを確保する場合は、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

お礼

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

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

回答No.6

Wendy02

#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関数は考慮しなくても大丈夫です。

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

回答No.5

hotosys

手っ取り早く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

お礼

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

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

回答No.3

cistronezk

＞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

お礼

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

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

回答No.2

imogasi

同一シートなら
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は範囲名定義。
ーーー
シートごとの範囲にＦｏｒ　Ｅａｃｈ　Ｎｅｘｔを使うほか無いと思う。
Ｕｎｉｏｎメソッドは初心者には超えた課題かと思う。

お礼

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

回答No.1

xls88

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

お礼

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