EXCELVBAで最終行が決まらない表の計算

質問

VBAなども書き始めたの最近なので、説明がつたないかもしれませんがよろしくお願いします。

EXCELの下記のような表があります。

それぞれのセットに対して、個数・のし代・売上を、各月ごとに出す表になっています。

計算としては、B1に書き込まれる仕入れ値の値を、それぞれのセットの個数に掛け、
その値と、のし代を加えたものが売上となって計算されて表示されるようにし、
最終行の合計にはそれぞれのセットの売上の合計値が表示されます。

例えば、お皿セットの4月は150x5+200の答えの950がC5のセルに入ります。
このようにして、各セットの各月の売上が個数、もしくはのし代の欄に値があれば計算し、
合計の欄に答えが入るようにしたいのです。

セットの数は決まっていないため最終行は変動し、個数、もしくはのし代の値が入っているのであれば、
求められるようにしたいのです。
ただし、1年ごとなので、N列までになります。

A 　　　　　　　B　　　　　　　C　　　　　　D　　　　　　E 　　　　　　・・・ N
1 仕入れ値　　150
2　　　　　　　　　　　　　　　　　4月　　　　　5月　　　　 6月　　　　　・・・3月
3 お皿セット　　個数　　　　　　5　　　　　　10　　　　　10
4　　　　　　　　　のし代　　　　200　　　　　400　　　　200
5　　　　　　　　　売上
6 箸セット　　　個数　　　　　　　3　　　　　　5　　　　　　6
7　　　　　　　　　のし代　　　　100　　　　　　　　　　　　300
8　　　　　　　　　売上
9 茶碗セット　　個数　　　　　　2　　　　　　　　　　　　　　3
10　　　　　　　　のし代　　　　100　　　　　100　　　　　100
11　　　　　　　　 売上
　　　・　　　　　　　・　　　 　　　・　　　　　　　　　・　　　　　　・　　　　　・
　　　・　　　　　　　・　　　 　　　・　　　　　　　　　・　　　　　　・　　　　　・
　　　・　　　　　　　・　　　 　　　・　　　　　　　　　・　　　　　　・　　　　　・

　合計

関数などを使って、別シートに一旦答えを出して・・・と行っていたのですが、
表の中に計算式があると、入力した値を消した時に式が消えてしまうので
Do　～Loopなどを使用することを考えました。

しかし、簡単な空白や最終行が決まったものしか書いたことが無く、
今回のような空白の行が点在し（個数無しでのし代あり、個数ありでのし代なしなど）途中にもあり、
2つおきに計算値が入るため途方にくれてしまいました。

1度で求められなくても良いので、マクロで作成できないものでしょうか。

回答No.1

mu2011 · ベストアンサー

一例です。
該当シートタブ上で右クリック→コードの表示→以下のコードを貼り付け→Ｆ５キー押下

Sub sample()
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row Step 3
For j = 1 To 12
Cells(i + 2, j + 2).Value = Cells(1, 2) * Cells(i, j + 2) + Cells(i + 1, j + 2)
Next
Next
End Sub

お礼

ありがとうございます。
まさしく、求めたい値が得られました。
End(xlDown)では空白行があると得られないので困っていたのですが、Rows.CountとEnd(xlUp).Row を使用することで、最終行が特定されないものについての繰り返し演算が得られるのですね。
ありがとうございました。

回答No.2

FEX2053

あの・・・セットの数の最大は「だいたい」決まってませんか？

そうならば、予測されるセットの数分、3月までの全てのエリアに計算式を突っ込んでおけばOKではないですか？

データが無ければその分の「売り上げ」はゼロになりますから、計算式としていくら足してあっても影響は出ませんし、逆に入力すればリアルタイムで合計値が出てきます。

ゼロを表示させたくないなら、表全体に、「セルの書式設定」で「ユーザー定義」を選び、「0;-0;;@」と指定すればゼロは出てきませんよ。
http://office.microsoft.com/ja-jp/excel-help/HP010070461.aspx

お礼

回答ありがとうございます。
もちろん、セルに計算式を入れて行うことが可能なのはわかっていて、ゼロ値も出ないことは承知していて使用していたのですが、入力した数値と一緒に数式を消してしまうというトラブルが続発して、誰が使用してもトラブルの少ないようにとの指示があり、試行錯誤していたところでした。