【Excel VBA】行の位置が変わる特定のセルを基準にして計算範囲を求める

知り合いの方から Excel VBA について質問をいただいたので、ブログネタにすることにしました。

質問内容

質問内容は次の通りです。

A 列に「合計」と出てきたら、その行と同じ B 列のセルに B 列の合計値を入れたい（C 列、D 列・・・も同様にしたい）。
でも「合計」の行が毎回違うので、どのようにすればいいのか分からない。

たとえば下記のようなデータのことですね。下記の表は、あるコンビニの１店舗を訪れた客ごとに何を何点買ったかというデータをまとめたもの（という想定）です。

このようなデータが２０以上の支店から毎日、コンビニの本部長のところに送られてきて、品物の種類ごとの合計値を本部長が計算しなければならないとします。

本部長は VBA で合計値を求めるプログラムを作ろうとしましたが、支店ごとに客の数（行数）は違いますし、同じ支店でも客数は日によって変わります。つまり、データごとに計算範囲が変わるし、合計値を入力する行も変わってしまうので、本部長から「どうすれば良いの？」とご相談を受けた、と仮定します。

特定のテキストの行番号を調べて、縦方向の計算をする

やり方はいろいろ考えられますが、プログラムで「合計」テキストの行の位置を探して対応するコードの例を下記に掲載します。

Option Explicit

Sub Calc()

'何行目から計算するか
Dim CalcStartRow As Integer: CalcStartRow = 3 '★

'何列目から計算するか
Dim CalcStartColName As String: CalcStartColName = "B" '★名前で指定したほうが分かりやすい
Dim CalcStartCol As Integer: CalcStartCol = Range(CalcStartColName & "1").Column

'何列目まで計算するか
Dim CalcEndColName As String: CalcEndColName = "G" '★名前で指定したほうが分かりやすい
Dim CalcEndCol As Integer: CalcEndCol = Range(CalcEndColName & "1").Column

'「合計」がある列
Dim GokeiTextCol As Integer: GokeiTextCol = 1 '★

'「合計」がある行（変動するので検索して探す）
Dim GokeiTextRow As Integer: GokeiTextRow = 0

'「合計」が何行目にあるかの検索を開始する行
Dim GokeiSearchStartRow As Integer: GokeiSearchStartRow = 4 '★

'「合計」が何行目にあるかの検索をあきらめる行
Dim GokeiSearchEndRow As Integer: GokeiSearchEndRow = 100 '★

'合計が何行目にあるかの検索を実行
Dim CurRow As Integer
For CurRow = GokeiSearchStartRow To GokeiSearchEndRow
    If Cells(CurRow, GokeiTextCol).Text = "合計" Then
        GokeiTextRow = CurRow '「合計」が見つかった
        Exit For
    End If
Next

'「合計」が見つからなかったら中断する
If GokeiTextRow < GokeiSearchStartRow Then
    Call MsgBox("「合計」が見つかりませんでした。", vbExclamation)
    Exit Sub '終了
End If

'「合計」の行に計算式を設定する
Dim CurCol As Integer
For CurCol = CalcStartCol To CalcEndCol '列ごとに計算式を設定していく
    'その列の計算を開始するセル
    Dim oCalcStartCell As Range: Set oCalcStartCell = Cells(CalcStartRow, CurCol)
    'その列の計算を終了するセル
    Dim oCalcEndCell As Range: Set oCalcEndCell = Cells(GokeiTextRow - 1, CurCol)
    '合計を求める計算式を設定
    Cells(GokeiTextRow, CurCol).Formula = "=SUM(" & oCalcStartCell.AddressLocal & ":" & oCalcEndCell.AddressLocal & ")"
Next

Call MsgBox("終了しました。", vbInformation)

End Sub

ご自分の環境（データの状態）に合わせて設定を変えるところに★を入れておきました。

実行すると下記のように、合計値が書き込まれます。これで、データによって客の数（行数）が変わっても、同じ VBA プログラムで対応できます。

シート上の「合計」のテキスト位置を求めるには、他には Find を使う手もありますし、
または「合計」を探さなくても、データの終了行まで（そして終了列まで）をプログラムで判定して計算する方法も考えられますね。

上記のコードでは、テキスト位置の検索方法だけでなく、他にもいくつか使えるテクニックが入ってると思いますので、じっくり（のんびり）眺めてご参考にしてみてください。

不明点があれば遠慮なく聞いてください。またのご相談、お待ちしています！(^^)/

特定のテキストの行番号を調べて、横方向の計算をする

（2010年10月17日追記。鈴木様、コメント欄からありがとうございます！^^）

本部長から追加の質問がありました。

「お客様それぞれの合計購入商品数」も出したいのだが？

とのことです。つまり、今度は表の横方向の計算をすることになります。

前述のプログラムコードで必要な行番号や列番号の多くがすでに取得できているので、それを利用すれば楽ですね。「合計」のテキストの行番号を調べるコードも既にできているので、その行の直前の行まで計算式をセットするようにしてみました。

５４行目からコードを追加したのでご参照ください。

Option Explicit

Sub Calc()

'何行目から計算するか
Dim CalcStartRow As Integer: CalcStartRow = 3 '★

'何列目から計算するか
Dim CalcStartColName As String: CalcStartColName = "B" '★名前で指定したほうが分かりやすい
Dim CalcStartCol As Integer: CalcStartCol = Range(CalcStartColName & "1").Column

'何列目まで計算するか
Dim CalcEndColName As String: CalcEndColName = "G" '★名前で指定したほうが分かりやすい
Dim CalcEndCol As Integer: CalcEndCol = Range(CalcEndColName & "1").Column

'「合計」がある列
Dim GokeiTextCol As Integer: GokeiTextCol = 1 '★

'「合計」がある行（変動するので検索して探す）
Dim GokeiTextRow As Integer: GokeiTextRow = 0

'「合計」が何行目にあるかの検索を開始する行
Dim GokeiSearchStartRow As Integer: GokeiSearchStartRow = 4 '★

'「合計」が何行目にあるかの検索をあきらめる行
Dim GokeiSearchEndRow As Integer: GokeiSearchEndRow = 100 '★

'合計が何行目にあるかの検索を実行
Dim CurRow As Integer
For CurRow = GokeiSearchStartRow To GokeiSearchEndRow
    If Cells(CurRow, GokeiTextCol).Text = "合計" Then
        GokeiTextRow = CurRow '「合計」が見つかった
        Exit For
    End If
Next

'「合計」が見つからなかったら中断する
If GokeiTextRow < GokeiSearchStartRow Then
    Call MsgBox("「合計」が見つかりませんでした。", vbExclamation)
    Exit Sub '終了
End If

'「合計」の行に計算式を設定する
Dim CurCol As Integer
For CurCol = CalcStartCol To CalcEndCol '列ごとに計算式を設定していく
    'その列の計算を開始するセル
    Dim oCalcStartCell As Range: Set oCalcStartCell = Cells(CalcStartRow, CurCol)
    'その列の計算を終了するセル
    Dim oCalcEndCell As Range: Set oCalcEndCell = Cells(GokeiTextRow - 1, CurCol)
    '合計を求める計算式を設定
    Cells(GokeiTextRow, CurCol).Formula = "=SUM(" & oCalcStartCell.AddressLocal & ":" & oCalcEndCell.AddressLocal & ")"
Next

'------ お客様それぞれの合計購入商品数 -----

'お客様それぞれの合計購入商品数の式を入れる列
Dim ShohinsuGokeiColName As String: ShohinsuGokeiColName = "H" '★名前で指定したほうが分かりやすい
Dim ShohinsuGokeiCol As Integer: ShohinsuGokeiCol = Range(ShohinsuGokeiColName & "1").Column

For CurRow = CalcStartRow To GokeiTextRow - 1
    'その行の計算を開始するセル
    Set oCalcStartCell = Cells(CurRow, CalcStartCol)
    'その行の計算を終了するセル
    Set oCalcEndCell = Cells(CurRow, CalcEndCol)
    '合計を求める計算式を設定
    Cells(CurRow, ShohinsuGokeiCol).Formula = "=SUM(" & oCalcStartCell.AddressLocal & ":" & oCalcEndCell.AddressLocal & ")"
Next

Call MsgBox("終了しました。", vbInformation)

End Sub

これを実行すると下記のようになります。

機能をサブルーチンに分ける

本部長からまたご連絡をいただきました。（2010年10月17日追記）

お弁当からお菓子までの合計を「食品合計」として I 列、お酒から雑誌までの合計を「その他」として J 列に入れるにはどうしたら良いか？

というご質問です。

基本的には前述のコードの「お客様それぞれの合計購入商品数」の部分（５４行目～６７行目）と同じことをあと２回やれば良いわけですが、似たような処理のコードを３回も同じ関数内に書くのはあまりスマートなやり方ではないですね。そういう書き方をし続けるとコード全体が見辛くなり、あとあとメンテもやりにくくなります。

というわけで、そろそろ前述のコードをいくつかのサブルーチンに分けつつ、今回の本部長のご希望に沿うよう対応してみます。

コードをサブルーチンに分けると下記のようなメリットがあります。

コード全体が整理されて見やすくなる
１つの関数の行数が短くなり、処理内容を把握しやすくなる
似たような処理を何回も記述せずに済む
特定の処理を行う関数を部品として再利用できるようになる（別のプログラムを作るときにも利用できる）

だいたいこんなところかな？サブルーチンに分割する際の注意点として、メインとなるルーチンを見るだけで全体の処理の流れを把握できるようにすることが大切です。

サブルーチンに分割して、本部長のご意向を反映したコードを下記に記載します。

Option Explicit

Sub Calc()

'何行目から計算するか
Dim CalcStartRow As Integer: CalcStartRow = 3 '★

'「合計」のテキストがある行を求める　★「合計」がある列、「合計」の検索を開始する行、「合計」の検索をあきらめる行を指定
Dim GokeiTextRow As Integer: GokeiTextRow = GetGokeiTextRow(1, CalcStartRow + 1, 100)

'「合計」が見つからなかったら中断する
If GokeiTextRow <= CalcStartRow Then
    Call MsgBox("「合計」が見つかりませんでした。", vbExclamation)
    Exit Sub '終了
End If

'商品ごとの商品数の合計を求める  ★計算開始列の名前、計算終了列の名前を指定
Call ShokuhinsuGokei_PerItem(CalcStartRow, GokeiTextRow, "B", "G")

'客ごとの商品数（全商品）の合計を求める ★計算開始列の名前、計算終了列の名前、計算式入力列の名前を指定
Call ShokuhinsuGokei_PerCustomer(CalcStartRow, GokeiTextRow - 1, "B", "G", "H")

'客ごとの商品数（食品）の合計を求める ★計算開始列の名前、計算終了列の名前、計算式入力列の名前を指定
Call ShokuhinsuGokei_PerCustomer(CalcStartRow, GokeiTextRow - 1, "B", "E", "I")

'客ごとの商品数（その他）の合計を求める ★計算開始列の名前、計算終了列の名前、計算式入力列の名前を指定
Call ShokuhinsuGokei_PerCustomer(CalcStartRow, GokeiTextRow - 1, "F", "G", "J")

Call MsgBox("終了しました。", vbInformation)

End Sub

'==========================================================================

'「合計」のテキストがある行を求める
Function GetGokeiTextRow(GokeiTextCol As Integer, GokeiSearchStartRow As Integer, GokeiSearchEndRow As Integer) As Integer

Dim GokeiTextRow As Integer: GokeiTextRow = 0

'合計が何行目にあるかの検索を実行
Dim CurRow As Integer
For CurRow = GokeiSearchStartRow To GokeiSearchEndRow
    If Cells(CurRow, GokeiTextCol).Text = "合計" Then
        GokeiTextRow = CurRow '「合計」が見つかった
        Exit For
    End If
Next

GetGokeiTextRow = GokeiTextRow

End Function

'==========================================================================

'商品ごとの商品数の合計を求めるサブルーチン

Sub ShokuhinsuGokei_PerItem(CalcStartRow As Integer, GokeiTextRow As Integer, CalcStartColName As String, CalcEndColName As String)

'何列目から計算するか
Dim CalcStartCol As Integer: CalcStartCol = ColName2ColNumber(CalcStartColName)

'何列目まで計算するか
Dim CalcEndCol As Integer: CalcEndCol = ColName2ColNumber(CalcEndColName)

'「合計」の行に計算式を設定する
Dim CurCol As Integer
For CurCol = CalcStartCol To CalcEndCol '列ごとに計算式を設定していく
    'その列の計算を開始するセル
    Dim oCalcStartCell As Range: Set oCalcStartCell = Cells(CalcStartRow, CurCol)
    'その列の計算を終了するセル
    Dim oCalcEndCell As Range: Set oCalcEndCell = Cells(GokeiTextRow - 1, CurCol)
    '合計を求める計算式を設定
    Cells(GokeiTextRow, CurCol).Formula = "=SUM(" & oCalcStartCell.AddressLocal & ":" & oCalcEndCell.AddressLocal & ")"
Next

End Sub

'==========================================================================

'客ごとの商品数の合計を求めるサブルーチン

Sub ShokuhinsuGokei_PerCustomer(CalcStartRow As Integer, CalcEndRow As Integer, CalcStartColName As String, CalcEndColName As String, ShohinsuGokeiColName As String)

'何列目から計算するか
Dim CalcStartCol As Integer: CalcStartCol = ColName2ColNumber(CalcStartColName)

'何列目まで計算するか
Dim CalcEndCol As Integer: CalcEndCol = ColName2ColNumber(CalcEndColName)

'商品の合計を求める式を入力する列
Dim ShohinsuGokeiCol As Integer: ShohinsuGokeiCol = ColName2ColNumber(ShohinsuGokeiColName)

Dim CurRow As Integer
Dim oCalcStartCell As Range
Dim oCalcEndCell As Range

For CurRow = CalcStartRow To CalcEndRow
    'その行の計算を開始するセル
    Set oCalcStartCell = Cells(CurRow, CalcStartCol)
    'その行の計算を終了するセル
    Set oCalcEndCell = Cells(CurRow, CalcEndCol)
    '合計を求める計算式を設定
    Cells(CurRow, ShohinsuGokeiCol).Formula = "=SUM(" & oCalcStartCell.AddressLocal & ":" & oCalcEndCell.AddressLocal & ")"
Next

End Sub

'==========================================================================

'列名を列番号に変換する
Function ColName2ColNumber(ColName As String) As Integer

Dim ColNumber As Integer: ColNumber = Range(ColName & "1").Column

ColName2ColNumber = ColNumber

End Function

このコードを実行すると下図のようになります。