VBAでセル入力の数式に変数を用いたい

VBAで、下記"=SUM(F2:JY2)"の列のところを変数にしたいのですが、どのようにすれば
良いでしょうか？

Range(Cells(2, lastCol + 1), Cells(lastRow, lastCol + 1)) = "=SUM(F2:JY2)"

行の場合は例えば
"=SUM(F2:JY" & lastRow & ")"
のようにしていますが、列で悩んでいます。

例
"=SUM(F2:JY2)"
↑JYを変数にしたい。

No.5 ベストアンサー 回答者： fujillin 回答日時： 2019/04/19 16:02

こんにちは

＞"=SUM(F2:JY" & lastRow & ")"
＞のようにしていますが、列で悩んでいます。
行番号の場合はそのままの数値が利用できるけれど、列の場合はAB等に変換しなくてはならないので困っているということでしょうか？

そういう面倒なことは、エクセルにやってもらうのが賢明かと思います。
例として、列番号である285がわかっていて、これから「JY]を導いて「F2:JY2」のセル範囲の文字列を作成したいという場合を想定します。

colNum = 285
addr = Range("F2").Resize(1, colNum - 5).Address(0, 0)
fmula = "=SUM(" & addr & ")"

MsgBox fmula　　' → "=SUM(F2:JY2)"

のような感じで、セル範囲の文字列化が可能です。
Addressプロパティのオプション指定で、絶対参照、相対参照やA1形式、R1C1形式を選択できますので、目的に合わせた形式で得ることができます。


一方、地道に、自前で変換することももちろん可能です。
列番号はAから始まる26進表記になっていますので、285/26、(285 mod 26)を計算して文字A（=Chr(65))からの順序をとれば可能そうです。
例えば、（以下は、少しインチキですが…）

colNum = 285
col = Chr(64 + Int(colNum / 26) ) & Chr(64 + (colNum Mod 26))
fmula = "=SUM(F2:" & col & "2)"
MsgBox fmula　　' → "=SUM(F2:JY2)"

となりますが、わざわざこんな面倒なことをやる必要もないですよね。
（上記は雰囲気を示すだけなので、実際には、桁数を判断して文字列を作成しないと、おかしなことが起こり得ます）

上記のような計算をするまでもなく、エクセルにやってもらえば
　MsgBox Cells(2, colNum).Address(0, 0)
で「JY2]を簡単に求めることができます。

この回答へのお礼

ありがとうございます。
今回ご教授頂きました文字列化は色々使えそうです。
助かりました。

お礼日時：2019/04/19 18:33

No.4 回答者： WindFaller 回答日時： 2019/04/18 21:58

要するに、左端列固定で、右端列が可変で数式SUM関数を入れたいということでしたら、これでいいのではないでしょうか?

>"=SUM(F2:JY2)"
>↑JYを変数にしたい。
右端は、必ず、RC[-1]になるわけです。 こういう場合は、R1C1型のほうが便利です。

'//標準モジュール
Sub EnterForumla()
'絶対相対参照
　Dim lastRow As Long
　Dim lastCol As Long

　lastRow = Cells(Rows.Count, 6).End(xlUp).Row
　lastCol = Cells(2, Columns.Count).End(xlToLeft).Column '列2行目基準とする
　Range(Cells(2, lastCol + 1), Cells(lastRow, lastCol + 1)).Formula = "=SUM(RC6:RC[-1])"
End Sub
'---------------------------------
'//両方とも相対参照の場合
Sub EnterForumla2()
'相対参照
　Dim lastRow As Long
　Dim lastCol As Long
　Dim Col As Long
　lastRow = Cells(Rows.Count, 6).End(xlUp).Row
　lastCol = Cells(2, Columns.Count).End(xlToLeft).Column '列は2行目基準とする
　Col = Range("F2", Cells(2, lastCol)).Columns.Count
　Range(Cells(2, lastCol + 1), Cells(lastRow, lastCol + 1)).Formula = "=SUM(RC[-" & Col & "]:RC[-1])"

End Sub

この回答へのお礼

相対参照を失念していました。
ありがとうございます。

お礼日時：2019/04/19 18:31

No.3 回答者： nouble1 回答日時： 2019/04/18 20:01

此方によると、

lastCol
は　Boolean値のようですが、
合ってますか？

https://docs.microsoft.com/ja-jp/office/vba/api/ …

この回答へのお礼

ありがとうございます。
lastColは変数として使用しています。

お礼日時：2019/04/19 18:30

No.2 回答者： tom04 回答日時： 2019/04/18 18:59

No.1です。

投稿後気づきました。
数式の範囲をA列からにしていましたね。

「A2」の部分を「F2」に変更してください。

どうも失礼しました。m(_ _)m

この回答へのお礼

ありがとうございました。
私の頭では思いつかない手法で参考になりました。

お礼日時：2019/04/19 18:28

No.1 回答者： tom04 回答日時： 2019/04/18 18:48

こんばんは！

一例です。

Dim myAd As String
Dim myCol As String

と変数を二つ増やし
　myAd = Cells(1, lastCol).Address(False, False)
　　Select Case Len(myAd)
　　　Case 2
　　　　myCol = Left(myAd, 1)
　　　Case 3
　　　　myCol = Left(myAd, 2)
　　　Case Else
　　　　myCol = Left(myAd, 3)
　　　End Select
　　　　Range(Cells(2, lastCol + 1), Cells(lastRow, lastCol + 1)) = "=SUM(A2:" & myCol & 2 & ")"

としてみてはどうでしょうか？

※　一旦、最終列1行目のセル番地（相対参照）をmyAd　に格納し
その文字列の長さで判断
2文字の場合は　左1文字
3文字の場合は　左2文字
そうでない場合は　左3文字を変数　myCol（文字列型）にし、
単純にそれをつなぎ合わせているだけです。m(_ _)m