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

Question

こんにちは。
ＶＢＡ初心者のものですが教えてください。

「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

Wendy02 · Accepted Answer

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

Wendy02 · Answer

#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

hotosys · Answer

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

cistronezk · Answer

＞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

imogasi · Answer

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

xls88 · Answer

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

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

こんばんは。

#4の回答者です。

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

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

同一シートなら

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

似たような質問が見つかりました

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング

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