エクセルVBAでSUM関数の位置をずらす

質問

いつもお世話になっております。
エクセル2013のVBAで質問です。
例えばJ192セルに　=SUM($M192:$AX192)　と入力されていたとします。
これの範囲の幅（38列分）はそのままで、下記のコードで得た　end_col　の列番号(CD)の位置まで列をずらすような指定の方法はありますか？ただし、end_col　は一定ではありません。

VBAで位置をずらして、このような数式に範囲を変えたいです。　=SUM($AS192:$CD192)

Sub test()
Dim end_col As Long
Cells(3, Cells(3, Columns.Count).End(xlToLeft).Column).EntireColumn.Select
end_col = Selection.Column
End Sub

すいませんが、ご教授ください。

回答No.2

cj_mover · ベストアンサー

こんにちは。

課題としては、Ｒ１Ｃ１形式での参照を利用するのが、直球と思います。
（行を指定せずとも相対参照できますし、、、。）
パッと思い付いた内の、状況によって使い分けできるようなものだけ、４例挙げてみます。
お奨めはSub Re8896257c、現在の数式の参照先を基準にしていますので応用が利きます。
Sub Re8896257dだけはＡ１形式での参照です。

Sub Re8896257a()　'　列数38固定、38-1=37列手前の列から始まる範囲をＲ１Ｃ１参照する
Dim end_col As Long
　　end_col = Cells(3, Columns.Count).End(xlToLeft).Column
　　Range("J192").FormulaR1C1 = "=SUM(RC" & end_col - 37 & ":RC" & end_col & ")"
End Sub

Sub Re8896257c()　 '　列数可変、現在の[数式の参照先]の列数を基準に範囲をＲ１Ｃ１参照する
Dim end_col As Long
Dim cnt_col As Long
　　end_col = Cells(3, Columns.Count).End(xlToLeft).Column
　　With Range("J192")
　　　　cnt_col = .Precedents.Areas(1).Columns.Count
　　　　.FormulaR1C1 = "=SUM(RC" & end_col - cnt_col + 1 & ":RC" & end_col & ")"
　　End With
End Sub

Sub Re8896257d()　 '　列数可変、現在の[数式の参照先]の最右の列位置を基準にOffsetで範囲をＡ１参照する
Dim end_col As Long
Dim fml_end_col As Long
Dim ref As String
　　end_col = Cells(3, Columns.Count).End(xlToLeft).Column
　　With Range("J192")
　　　　With .Precedents.Areas(1)
　　　　　　fml_end_col = .Columns(.Columns.Count).Column
　　　　　　ref = .Offset(, end_col - fml_end_col).Address(RowAbsolute:=False, ColumnAbsolute:=True)
　　　　End With
　　　　.Formula = "=SUM(" & ref & ")"
　　End With
End Sub

Sub Re8896257j()　 '　列数可変、現在の[数式文字列]から先頭列・最後列を求めＲ１Ｃ１参照する
Dim end_col As Long
Dim fml_1st_col As Long
Dim fml_end_col As Long
Dim fml As String
　　end_col = Cells(3, Columns.Count).End(xlToLeft).Column
　　With Range("J192")
　　　　fml = .FormulaR1C1
　　　　fml_1st_col = Val(Mid(fml, InStr(fml, "C") + 1))
　　　　fml_end_col = Val(Mid(fml, InStrRev(fml, "C") + 1))
　　　　.FormulaR1C1 = "=SUM(RC" & fml_1st_col + end_col - fml_end_col & ":RC" & end_col & ")"
　　End With
End Sub

お礼

できました。ありがとうございました。
今回はおすすめのを採用することにしました。

回答No.3

WindFaller

こんにちは。

マクロで、数式をいじるのは、面倒極まりないですね。
最初から、マクロで値を出したほうが楽です。
以下は、常識的なエラー対処はされています。

'//
Sub TestSetFormula()
　Dim EndCol As Long
　Dim i As Long
　Dim r As Range
　Dim r1 As Range
　Dim r2 As Range
　Dim sAdr As String
　With ActiveSheet
　　Set r = Range("J192")
　　EndCol = .Cells(3, Columns.Count).End(xlToLeft).Column
　　If EndCol < 2 Then MsgBox "データがありません。", 48: Exit Sub
　　If Not r.HasFormula Then MsgBox r.Address & " には数式がありません。", 48: Exit Sub
　　sAdr = r.DirectPrecedents.Address
　　Set r1 = Range(sAdr)
　　i = r1.Columns.Count　 'または、38の固定した定数なら、リテラルに書くか、定数を置きます。
　　If EndCol >= i Then
　　　Set r2 = .Cells(r1.Row, EndCol).Offset(, -i + 1).Resize(, i)
　　　r.FormulaLocal = "=SUM(" & r2.Address & ")"
　　End If
　End With
End Sub
'//

お礼

できました。ありがとうございました。

回答No.1

mshr1962

列の変更に対して、列を絶対参照にする必要性が意味ないと思うけど。。。

Sub test()
Dim end_col As Long
Cells(3, Cells(3, Columns.Count).End(xlToLeft).Column).EntireColumn.Select
end_col = Selection.Column

Cells(192,10).Formula = "=SUM("& Cells(192,end_col - 37).Address(RowAbsolute:=False) & ":" & Cells(192,end_col).Address(RowAbsolute:=False) & ")"

End Sub

お礼

できました。ありがとうございました。