Excelマクロ 任意の複数範囲で同じ処理

質問

お世話になります。
まだ自力ではコードをろくに書けないような初心者です。

dim 行番号 as integer
dim ws as worksheet

for each ws in worksheet
for 行番号 = 10 to 46
with ws.cells(行番号,8)　
.value = .offset(0,-4).value - .offset(0,-1).value
end with
next 行番号
next ws

この計算を、H列だけでなく、H列からAV列までで5列おきに行ないたく、コードを足しました。

----------
dim 行番号 as integer　
dim 列番号 as integer
dim ws as worksheet

for each ws in worksheet
for 行番号 = 10 to 46
for 列番号 = 8 to 48 step 5　
with ws.cells(行番号,列番号)　
.value = .offset(0,-4).value - .offset(0,-1).value
end with

next 行番号
next 列番号
next ws

----------

自分なりに考えて作ったのですが、実行すると「実行時エラー'13' 型が一致しません」と出ます。
8枚目くらいのシートで処理は止まっています。
黄色くなるのは　.value = .offset(0,-4).value - .offset(0,-1).value　ですが、
with ws.cells(行番号,列番号)の「列番号」に数字を入れるとエラーにならないので、計算ではなく範囲指定がまずいんだと思います。
（そもそも元のコードは別の質問でご教示いただいたものなので、そこが間違っているとは思えません。）
ヘルプを見ても難しく、理解できませんでした。

どのように修正すれば良いのか、教えていただけませんでしょうか。
現状、自分で思いつくのは列番号を変えて繰り返し記述することだけです。
なるべくシンプルに書く方法を知りたいので、ここで質問させていただきます。
ヒントだけでも結構です。
よろしくお願い致します。

回答No.5

hige_082 · ベストアンサー

別に、コードはおかしくありませんよ（怪しい所が、幾つかありますが）
７枚目のシートまでは、正常に処理されているのでしょ
と、言うことはデータのほうがおかしいのです

検証
A1=2、B1=1　を入力し test を実行した場合と
A1=ああ、B1=1　を入力し test を実行
「実行時エラー'13' 型が一致しません」が出たでしょう

Sub test()
Cells(1, 3).Value = Cells(1, 1).Value - Cells(1, 2).Value
End Sub

8枚目くらいのシートに、数値以外のデータが入力されたセルがあるということです

原因が分かった所で対策案です
質問者さんがエラーをどうしたいのか、解らないので
幾つか対策案を上げておきます

１　データを見直す
　　コードの修正はなし

２　エラーを無視する
　　On Error Resume Next　を使用する

On Error Resume Next　　'この辺りに追加するのが良いと思います
for each ws in worksheets

３　数値以外は処理しない
　　IsNumeric　を使用

with ws.cells(行番号,列番号)　
If IsNumeric(.offset(0,-4).value) And IsNumeric(.offset(0,-1).value) Then　　'このif文を追加
.value = .offset(0,-4).value - .offset(0,-1).value
endif　　'追加
end with

４　エラーのセルに色を付けて後で修正
　　３の応用編（どちらか一つでも数値で無い場合、両方のセルが赤になる）

with ws.cells(行番号,列番号)　
If IsNumeric(.offset(0,-4).value) And IsNumeric(.offset(0,-1).value) Then　　'このif文を追加
.value = .offset(0,-4).value - .offset(0,-1).value
else
.offset(0,-4).Interior.ColorIndex = 3　　'追加　セルを赤にする
.offset(0,-1).Interior.ColorIndex = 3　　'追加　セルを赤にする
endif　　'追加
end with

詳細はヘルプかWebで調べてね

お礼

ご指摘の通り、文字が入ったセルがありました。
コードは勉強して修正していきます。
ありがとうございました。

また、私は単純にエラーが出ないようコードを修正したいと思っていたのですが（間違っていたのはデータでしたが）、エラーを無視したりエラーのセルを明確にしたり、様々な選択ができるんですね。
恥ずかしい質問をしてしまったものの、非常に勉強になりました。
ありがとうございました。

回答No.4

okormazd

ちょっとしたミスがあるようだけど、
下記で問題なく動く。
質問のエラーの意味がわからない。

Sub test()

Dim 行番号 As Integer
Dim 列番号 As Integer
Dim ws As Worksheet

For Each ws In Worksheets　　　'ここがちょうっとちがっているよ。
For 行番号 = 10 To 46　　　　　
For 列番号 = 8 To 48 Step 5
With ws.Cells(行番号, 列番号)
.Value = .Offset(0, -4).Value - .Offset(0, -1).Value
End With

Next 列番号　　　　　'ここの順序がちがうけど　　　　　
Next 行番号　　　　　'
Next ws

End Sub

お礼

誤って文字を入れてしまっているセルがありました。
そこを修正しましたところ、正常に処理できました。

わざわざコードのおかしい点を教えてくださり、ありがとうございました。
勉強になります。

回答No.3

hallo-2007

No1です。勘違いしていました。
for 行番号 = 10 to 46
for 列番号 = 8 to 48 step 5　
ですので番号で異常はでないですね。
Mo2の方がおっしゃるように、文字列で引き算の可能性ですね。
デバックのモードでマウスを
.offset(0,-4).value へ移動させてみてください。

お礼

文字が入ったセルを見つけました。

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

回答No.2

xls88

空のシートでテストすると問題なく動きます。

.offset(0,-4).value - .offset(0,-1).value
上記計算セルのデータが、数値ではない、ということではないでしょうか。

お礼

ご意見ありがとうございます。
空のシートで試す、という方法があるんですね。
勉強になりました。

シートを片っ端から確認した結果、ご指摘の通り、文字が入った列を見つけました。
ありがとうございました。

回答No.1

hallo-2007

エラーが出て、黄色になったときに
with ws.cells(行番号,列番号)　
.value = .offset(0,-4).value - .offset(0,-1).value
end with
の行番号とか列番号の変数の部分にマウスを移動させてみてください
どのような値が入っているか表示されます。
もし列番号が3以下なら
.offset(0,-4).value がエラーになりますね。左に４移動(-4)が出来ないですから。