Excelマクロ 複数のシートそれぞれで計算

質問

マクロの勉強を始めて間もない初心者です。
手持ちの解説書やネットで探してみたのですが解決できなかったので質問させてください。

計算式を手打ちで入力する手間を省くため、コードを書きました。
(不要かもしれませんが、一応記してみます。）

sub 計算()

dim 行番号 as integer

for 行番号 = 10 to 46
cells(行番号,4).select
with activecell
.value=.offset(-2,0).value-.offset(-1,0)
.offset(1,0).select
end with
next

end sub

マクロを実行すると、1枚目のシートでのみ、この計算がなされます。
が、私はこの計算を、複数の（もしくは全ての）シートで行いたいと考えています。
シートのフォーマットは全く同じで、入力してある数値が違うだけです。

シートが20枚以上あり、1枚ずつ選択して同じコードを書くには、コピー&ペーストを使うにしてもしんどいので、検索してみたのですが。。
調べて試してみた sheets.select や、 sheets(array("シート名1","シート名2"…).select では、セル値が異なるにもかかわらず、シート1の計算結果が以降のシートにも反映されてしまいました。

コードは共通でも各シートで独立した計算結果を導くには、どうすれば良いのでしょうか？
お詳しい方、ご教示くださいますようよろしくお願い致します。

回答No.4

onlyrom · ベストアンサー

'----------------------------------------------
Sub 計算()
　Dim 行番号 As Integer
　Dim mySheet As Worksheet

　For Each mySheet In Worksheets
　　　For 行番号 = 10 To 46
　　　　　With mySheet.Cells(行番号, 4)
　　　　　　.Value = .Offset(-2, 0).Value - .Offset(-1, 0).Value
　　　　　End With
　　　Next 行番号
　Next mySheet

End Sub
'-----------------------------------------------

Select,Activateは必要な場合を除いて省くようにしましょう。
データが多いときはスピード、何よりもコードが見易くシンプルになります。
どういう時にそれらを省けるのか理解することは
ＶＢＡを理解する上で非常に役立つものだと考えます。
以上。

回答No.8

onlyrom

またまた登場、onlyromです。　←　登場し過ぎ、(^^;;;

No6さん、アドバイスのApplication.ScreenUpdatingについて
あとひとつ大事なことを書き忘れていました。

画面のチラつきについては先にコメントしましたが
処理スピードの面からのいうと
●大量のデータをセルに入れたり出したりする●場合には
画面の更新をさせない方が明らかにスピードアップになりますので
Application.ScreenUpdating=falseを入れた方がいいことになります。
これは先に提示したようなコードでも言えることです。

折角ですからそこら辺りも考えてみると面白くＶＢＡを学べるのではないでしょうか。

お礼

何度もご回答いただきましたので、失礼とは存じますが、ここでまとめてお礼させてください。
いろいろと気に掛けていただき、ありがとうございました。

まず、私が作ったコードは参考書を元にしており、恥ずかしながら、それ自体は「完璧」だと思っていました。
まだ省く余地があったとは思いもよらず、マクロの奥深さに改めて驚くやら、悔しいやら。
また、diashunさんからご教示いただきました画面制御の件につきましても、「シートが多いときはこれが要るんだな」とまたしても思い込んでおりました。
ご意見ありがとうございました。

数回に亘るご意見を入力するのにかけていただいたお時間、及び私のような初心者に分かるように言葉も選んでくださったことに感謝いたします。
たくさん勉強させていただき、ありがとうございました。

回答No.7

onlyrom

No4,onlyromです。

No6さん、少しもお節介ではありませんよ。
質問者に的確なアドバイス回答をすることは大切なことですから。

●No6さんよりアドバイスのApplication.ScreenUpdatingについて

それは特に他のシートを操作する場合等の画面のチラつきを抑える役目があるのですが、
常に必要かというとそうではなく、必要な場合とそうでない場合があります。

質問者の提示したようなコードでは、それがないと画面がチラつきますが、
当方が提示したようなコードでは、それがなくても画面がチラつくことはありません。

ちょうどいい具合にNo3さんが質問者のコードを加筆してくださってますので、
それと当方のコードを実行してみてください。
画面のチラつきとはどういうことか分かると思います。

ScreenUpdatingプロパティが必要なのかそうでないのか
そこら辺りが分かるようになると
より一層ＶＢＡの理解が深まっていくものと思われます。

回答No.6

diashun

No.4 の方の回答にプラスで、
シートが20ほどあるとの事なので、VBAを実行する上でExcellの実行画面にかなりのチラつきが予想されます。
そこで、VBA実行文の最初と最後に画面制御のお作法のコードが必要では？
'実行前
Application.ScreenUpdating = False
'実行後
Application.ScreenUpdating = True

お節介かもしれませんが・・・以上です。

お礼

お節介などとはとんでもないです。
アドバイス、ありがとうございました。

マクロを使う=ぶれるものだと思っていました。。
ひとつ勉強になりました。
ありがとうざいます。

回答No.5

Wendy02

こんばんは。

　　.value=.offset(-2,0).value-.offset(-1,0)

この計算式は意味が良く分からないです。
　2行前のセルから、1行前のセルを引いて、下のセルに値を入れていく、というのは循環参照ではないけれども、計算したものに対して、計算をしています。

　.Value = .Offset(0,-2).Value-.Offset(0,-1).Value

　これなら、意味が分かります。

>調べて試してみた sheets.select や、 sheets(array("シート名1","シート名2"…).select では、セル値が異なるにもかかわらず、シート1の計算結果が以降のシートにも反映されてしまいました。

　作業グループにして行うのは、数式を一括して入れる方法です。シートが何枚あっても、一回で済みます。しかし、数式から値にすることができません。マクロのコードについては、とりあえず割愛させていただきます。

お礼

計算式は書き間違いです、すみませんでした。

作業グループについてお教え頂き、ありがとうざいます。
なぜ駄目だったのか、理解しました。
ありがとうございました。

回答No.3

merlionXX

こんな感じかな。

Sub 計算()

Dim 行番号 As Integer
Dim ws As Worksheet

For Each ws In Worksheets
ws.Activate

For 行番号 = 10 To 46
Cells(行番号, 4).Select
With ActiveCell
.Value = .Offset(-2, 0).Value - .Offset(-1, 0)
.Offset(1, 0).Select
End With
Next

Next ws

End Sub

お礼

ご回答いただき、ありがとうざいました。

私が作ったものにうまく補足してくださったので、非常に分かりやすかったです。
勉強になりました。
ありがとうございました。

回答No.2

hallo-2007

参考です。
Sub Test()
n = Worksheets.Count'シートの数を数える
For i = 1 To n'最初のシートから最後のシートへ順番に
Sheets(i).Select
MsgBox i
Next
End Sub

お礼

ご意見ありがとうございました。

今の私にはまだ、分かるような分からないような…という印象です。
今後勉強して、ご提示いただいたコードを理解できるようになりたいと思います。
ありがとうございました。

回答No.1

web2525

シートの数だけループさせれば良いのでは？

お礼

ご意見ありがとうございます。

勉強不足で、ループと言われても具体的にどうすれば良いのか現段階ではまったく分かりません。
すみません。
今後引き続き勉強し、「ああ、そうか」と頷けるようになりたいと思います。

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