いつもお世話になっております。
エクセル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ベストアンサー
- 回答日時:
こんにちは。
課題としては、R1C1形式での参照を利用するのが、直球と思います。
(行を指定せずとも相対参照できますし、、、。)
パッと思い付いた内の、状況によって使い分けできるようなものだけ、4例挙げてみます。
お奨めはSub Re8896257c、現在の数式の参照先を基準にしていますので応用が利きます。
Sub Re8896257dだけはA1形式での参照です。
Sub Re8896257a() ' 列数38固定、38-1=37列手前の列から始まる範囲をR1C1参照する
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() ' 列数可変、現在の[数式の参照先]の列数を基準に範囲をR1C1参照する
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で範囲をA1参照する
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() ' 列数可変、現在の[数式文字列]から先頭列・最後列を求めR1C1参照する
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
- 回答日時:
こんにちは。
マクロで、数式をいじるのは、面倒極まりないですね。
最初から、マクロで値を出したほうが楽です。
以下は、常識的なエラー対処はされています。
'//
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
- 回答日時:
列の変更に対して、列を絶対参照にする必要性が意味ないと思うけど。
。。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
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 最終列の右へSUM関数を作成するため下記コードを実行しましたが、最終列「10月28日」が上書きされて 3 2022/12/05 20:32
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
- Excel(エクセル) 指定文字列が該当するA列をアクティブセルにするには 3 2022/08/17 13:18
- Visual Basic(VBA) 【VBA】写真の貼り付けコードがうまく機能しません。 5 2022/09/01 18:43
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Visual Basic(VBA) 複数csvを横に追加していくマクロについて 2 2023/04/25 09:19
- Visual Basic(VBA) フォルダの場所を可変にしたいです(マクロ) 4 2023/05/11 10:00
- Excel(エクセル) 日付で矢印マクロ 4 2023/07/25 16:47
- Visual Basic(VBA) エクセルVBA ダブルクリックしたら色反転を指定したセルのみにしたい 2 2022/04/06 12:52
関連するカテゴリからQ&Aを探す
おすすめ情報
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
関係演算子が無効です。
-
9iのSQL:1999構文を使用した外...
-
GROUP BYを行った後に結合した...
-
SELECTで1件のみ取得するには?
-
Excelで、改行がある場合の条件...
-
Oracleでの文字列連結サイズの上限
-
SQL文で右から1文字だけ削除す...
-
Accessで別テーブルの値をフォ...
-
ADO VBA 実行時エラー3021
-
SQLの書き方(チェックボックス)
-
実績累計の求め方と意味を教え...
-
OracleのSQL*PLUSで、デー...
-
アクセスでレポートの1印刷内...
-
Accessでのレコード存在チェック
-
CREATE テーブルでの複数外部...
-
oracleのinsert select性能
-
チェックボックスの項目をDBにi...
-
Access終了時の最適化が失敗?
-
キーが同じを複数行を1行にま...
-
select insertで複数テーブルか...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
関係演算子が無効です。
-
SQL 外部結合についての質問
-
Excel VBAで、Averageの範囲を...
-
TO_CHAR関数における外部結合に...
-
同じテーブルをLEFT JOIN
-
自己相関サブクエリと自己結合...
-
LEFT JOINの条件式で=’’を使用...
-
Informixで文字列を数値型に
-
内部結合する時の結合条件と制...
-
Access 結合条件設定方法
-
T-SQL 動的に優先順ごとにデー...
-
WHERE句の書き方
-
エラー「無効な識別子です」
-
エクセルVBAでSUM関数の位置を...
-
Pro*CでVARCHAR2項目の検索
-
エラー2「無効な識別子です」
-
Oracle SQL 結合の手法について
-
【修正希望】【マクロ】元デー...
-
SQLで後方の文字列を置換する方法
-
range表記をcells表記にしたい
おすすめ情報