こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

「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

投稿日時 - 2009-09-20 19:26:56

QNo.5305554

すぐに回答ほしいです

質問者が選んだベストアンサー

こんばんは。

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

お礼

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

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

投稿日時 - 2009-10-03 11:07:59

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(6)

ANo.6

#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

投稿日時 - 2009-09-21 10:41:13

お礼

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

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

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

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

投稿日時 - 2009-10-03 11:12:00

ANo.5

手っ取り早く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-09-20 22:44:46

お礼

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

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

投稿日時 - 2009-10-03 11:08:24

ANo.3

>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")」との2回に分けるのが素直なやり方です。

実は設定範囲がもっとたくさんある、というのなら、
・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-09-20 20:56:03

お礼

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

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

投稿日時 - 2009-10-03 11:03:22

ANo.2

同一シートなら
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
の15:22 つ以上の指定した範囲を選択する方法
にあるように、「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は範囲名定義。
ーーー
シートごとの範囲にFor Each Nextを使うほか無いと思う。
Unionメソッドは初心者には超えた課題かと思う。

投稿日時 - 2009-09-20 20:49:29

お礼

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

投稿日時 - 2009-10-03 11:00:57

ANo.1

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

投稿日時 - 2009-09-20 20:14:03

お礼

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

投稿日時 - 2009-10-03 10:59:00

あなたにオススメの質問