【初月無料キャンペーン中】gooドクター

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

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

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


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

gooドクター

A 回答 (5件)

こんにちは



>"=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]を簡単に求めることができます。
    • good
    • 1
この回答へのお礼

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

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

要するに、左端列固定で、右端列が可変で数式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
    • good
    • 1
この回答へのお礼

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

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

此方によると、


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

https://docs.microsoft.com/ja-jp/office/vba/api/ …
    • good
    • 1
この回答へのお礼

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

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

No.1です。



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

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

どうも失礼しました。m(_ _)m
    • good
    • 0
この回答へのお礼

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

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

こんばんは!



一例です。

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
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

gooドクター

このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング