【VBA】値のあるセルを取得してSUM関数で計算～ワークシート関数の使い方～

値の入っているセル位置を取得してSUM関数を利用した絶対参照で計算をしていきます。

今回はSUM関数を例として記述していますが、どのワークシート関数でも同じように値を出力することができますので、利用したい関数に書き換えて使用してみてください。

この記事で分かることは以下になります。

データのある最初のセル位置の取得
データのある最終行のセル位置の取得
ワークシート関数をVBAで利用する方法

値の入っているセルの位置を取得する
セルにワークシート関数を代入するFormulaの使い方
作成したコードで計算をしてみる
WorksheetFunctionでワークシート関数を使うこともできる

値の入っているセルの位置を取得する

SUM関数で計算するにはセルの位置を指定する必要があります。最初にセルの位置を取得して計算する範囲を決めましょう。

前提
上記の表B列の年齢を合計します。

値の入っているセルの最初の位置を取得する

StartCell = Cells(1, 2).End(xlDown).Row + 1

セルの開始位置を指定して下に向かって最初の値が入力されている位置を取得する構文です。

セルの位置は変数StartCellに代入して保持するようにします。

実際に変数StartCellに格納されている値をDebug.printで確認してみます。

変数StartCellを確認する構文

Sub test()
   Dim StartCell As Long
    StartCell = Cells(1, 2).End(xlDown).Row + 1
   Debug.Print StartCell
End Sub

出力結果

出力結果が3と出ました。表からもわかる通り値が格納されている最初の行は表のヘッダーである”age”(2行目)です。その為、本来は2と出力されますがヘッダーを除外するため＋1としたため、3と出力されます。

これでヘッダー(2行目のage)を除いた最初の行番号を取得することができました。

注意

開始位置として指定するセルは空白である必要があります。指定したセルに値が入力されている場合には値の入っている最終行の位置を取得してしまいます。

値の入っているセルの最後の位置を取得する

今度は値のある最終行を取得していきます。構文は以下の通りです。

EndCell = Cells(Rows.Count, 2).End(xlUp).Row

セルの最終行を取得して上方向に値の入っているセルを確認していきます。

セルの位置は変数EndCellに代入して保持するようにします。

同様に変数EndCellに格納されている値をDebug.printで確認してみます。

変数EndCellを確認する構文

Sub test()
Dim EndCell As Long
 EndCell = Cells(Rows.Count, 2).End(xlUp).Row
Debug.Print EndCell
End Sub

出力結果

出力結果が5と出ました。表の最終行は5行目のため、正確に取得できたことがわかります。

これで開始行と最終行がどちらも取得できました。

最終行や最終列取得の詳細は下記を参照

VBAで値の入っている最終行と最終列を取得する

最終行と最終列の取得方法について紹介していきます。ワークシートで利用できるショートカットキー『Ctrl ＋上下左右の...

セルにワークシート関数を代入するFormulaの使い方

Formulaプロパティで計算式を代入していきます。

Cells(5, 3).Formula = 数式

数式を代入する位置を指定して、その指定した位置に対して数式を代入します。

数式の書き方

VBAのコードとしてのSUM関数

 "=SUM(" & Cells(開始位置, 1).Address & ":" & Cells(終了位置, 1).Address & ")"

今回はSUM関数を利用するのですがほかの関数でも考え方は同じです。上記の開始位置・終了位置となるのは前章で値を取得したStartCellとEndCellです。

それぞれの変数を代入することでSUM関数が完成します。

上記ではわかりにくいと思いますのでとりあえず通常のSUM関数を見ていきます。

通常のSUM関数

=SUM(B3:B5)

＝SUMとセルの位置を囲う()カッコ、範囲を指定する：コロンは文字列となるので(””)ダブルクォーテーションで囲う必要があります。

そしてセルの位置であるB3とB5は実際のコードでは変数で指定して、 Addressプロパティを利用してセルのアドレスを取得します。

変数と文字列はVBAでは＆を利用することで結合できます

これらを書き換えることでVBAのコードとしての数式が完成します。

作成した数式を引数として指定したセルに代入することで計算が可能になります。

作成したコードで計算をしてみる

コードを作成して実行していきます。

Sub test()
    Dim StartCell As Long, EndCell As Long
        StartCell = Cells(1, 2).End(xlDown).Row + 1
        EndCell = Cells(Rows.Count, 2).End(xlUp).Row

        Do Until IsNumeric(Cells(StartCell, 2).Value)
          StartCell = StartCell + 1
        Loop
      
        Cells(3, 5).Formula = "=SUM(" & Cells(StartCell, 2).Address _
        & ":" & Cells(EndCell, 2).Address & ")"
End Sub

※コードの見方
白・・・コード
紫・・・コメント
青・・・プロシージャの宣言
その他・・・コードを見やすくするために使うかも

1行目に変数を定義しています。そのあとに開始の行と最後の行を取得していきます。

Do Untilは取得したセルの位置に入力されている値が数値になっているかを確認しています。なくても構いませんがカラムの項目名などがある場合は必要になります。

最後に数式をセルに代入しています。

出力結果

指定したセル(E3)に年齢の合計が出力されました。

SUM関数が代入されたセルは下記のように表示されます。

通常のSUM関数(ワークシート関数)を利用した場合と同じように表示されていることがわかります。

WorksheetFunctionでワークシート関数を使うこともできる

今回紹介した記述方法以外にもワークシート関数を利用する書き方があります。それがWorksheetFunctionを利用する方法です。

構文

Application.WorksheetFunction.Sum(開始位置,終了位置)

WorksheetFunctionを使用したサンプル
先ほど利用した表をWorksheetFunctionを利用した記述で計算していきます。

Sub test()

    Dim StartCell As Long, EndCell As Long
        StartCell = Cells(1, 2).End(xlDown).Row + 1
　　　　EndCell = Cells(Rows.Count, 2).End(xlUp).Row

Cells(3, 5) = Application.WorksheetFunction.Sum(Range(Cells(StartCell, 2), Cells(EndCell, 2)))

End Sub

この場合でも表の計算結果は28となり、formulaプロパティを使用した記述方法同様にSUM関数を利用することができます。