マクロで任意の列を参照して、オートフィルをする方法が知りたいです。

マクロで任意の列を参照して、オートフィルをする方法が知りたいです。

うまく説明できないかもしれないので、添付画像に沿って質問させていただきます。


**はじめに**

・A列とE列には、都道府県名があります。
・B列とC列、F列とG列にはそれぞれのデータがありますが、空白セル（行）もあります。
・今、D2セルをコピーして、H2セルに貼り付けました。

**ここからマクロ**

①　アクティブセルから「3列左の最終行」まで、下方向にオートフィルさせたいです。
　　※サンプルではH2セルを選択していますが、セル番地を固定するのではなく、「アクティブセル」を下方向にオートフィルさせます。
　　
②　データの行数は不規則なので、「都道府県」列の最終行に合わせてオートフィルさせます。


**備考**

マクロのコードで、

Dim x As Long
x = Range("E" & Rows.Count).End(xlUp).Row
Range("H2") = "=sum(F2:G2)"
Range("H2").AutoFill Destination:=Range("H2:" & "H" & x), Type:=xlFillSeries

というところまでは分かりました。

しかし、上記のコードは「参照する範囲をE列」に固定してありますが、
現在選択しているセル（今回はセルH2）から「3列左の列を参照」したいのです。

添付画像ではデータがA～H列までしかありませんが、
実際にはシートの右側に同様のデータが複数あります。

その為、オートフィルの終了位置を「○列を参照」と固定するのではなく、
「現在選択中のセルから3列左の列」と指定したいのです。

恐れ入りますが、どなたかお分かりの方がおられましたら、
ご教授いただけましたら幸いです。

宜しくお願い申し上げます。

質問者からの補足コメント

• すみません、とても言いにくいのですが…
  
  質問ではサンプルとして簡単な数式（sum）を記入しましたが、実際にはとても複雑な数式を使用しています。
  実際の数式をここに記入しようとしましたが、あまりにも複雑すぎて文字数制限をオーバーしました…
  
  
  もし可能であれば、数式が変わっても同様の作業ができるように、
  コードに数式となる部分を記述せず、
  *********************
  「何らかの数式を」セルH2に貼り付けた後、E列最終行までオートフィルさせる
  *********************
  というコードを教えて頂けましたら幸いです。
  
  せっかく数式を反映させるコードをご教授頂いたのに、本当に申し訳ありません。

  No.2の回答に寄せられた補足コメントです。 補足日時：2018/03/09 15:28

No.3 ベストアンサー 回答者： tom04 回答日時： 2018/03/09 16:08

続けてお邪魔します。

＞何らかの数式を」セルH2に貼り付けた後、E列最終行までオートフィルさせる・・・
選択したセルには数式が入っているという前提で・・・

Sub Sample3()
Dim myCol As Long, lastRow As Long
With Selection
myCol = .Column
If .HasFormula Then
lastRow = Cells(Rows.Count, myCol - 3).End(xlUp).Row
Range(Cells(2, myCol), Cells(lastRow, myCol)).Formula = .Formula
End If
End With
End Sub

ではどうでしょうか？

※　選択セルに数式が入っていないと何も変化しません。m(_ _)m

この回答へのお礼

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

イメージ通りの処理です。
このマクロは、今後多くの場面で応用できると思います。

tom04さんには、以前にもお世話になっていますが、
今回も本当に助かりました。

ありがとうございました。
('v'*)

お礼日時：2018/03/09 18:53

No.4 回答者： tom04 回答日時： 2018/03/09 17:55

何度もごめんなさい。

No.3のコードでは3行目以降を選択していると、数式の行がずれてしまいます。
No.3のコードも消去してください。

お示しの画像の通り、1行目は項目行でデータは2行目以降にあり
選択列の2行目には数式が入っているという前提のコードにしました。

Sub Sample4()
Dim myCol As Long, lastRow As Long
With Selection
myCol = .Column
If Cells(2, myCol).HasFormula Then
lastRow = Cells(Rows.Count, myCol - 3).End(xlUp).Row
Range(Cells(2, myCol), Cells(lastRow, myCol)).Formula = Cells(2, myCol).Formula
End If
End With
End Sub

※　これでどの行を選択しても、
その列の3列左の最終行までの数式が入ると思います。m(_ _)m

この回答へのお礼

あ、実はNo.3で教えて頂いたコードで実行したところ、1行ずれていました。

何となく、
Range(Cells(2, myCol),
の部分を
Range(Cells(3, myCol),
に変更したら正しい位置に計算結果が表示されたので、
「これでよし!」と思っていました。

もっと正しいコードがあったのですね。

ご丁寧な訂正、ありがとうございます。

今後とも、お力を貸して頂けましたら幸いです。

お礼日時：2018/03/09 21:35

No.2 回答者： tom04 回答日時： 2018/03/09 11:34

No.1です。

質問文を読み違えていました。
選択セルの列をそのままオートフィルで良かったのですね。
前回のコードは消去し↓のコードに変更してください。

Sub Sample2()
Dim lastRow As Long, myCol As Long
myCol = Selection.Column
lastRow = Cells(Rows.Count, myCol - 3).End(xlUp).Row
Cells(2, myCol).Formula = "=Sum(" & Cells(2, myCol - 2).Address(False, False) & _
":" & Cells(2, myCol - 1).Address(False, False) & ")"
Cells(2, myCol).AutoFill Destination:=Range(Cells(2, myCol), Cells(lastRow, myCol))
End Sub

※　数式を残すようにしていますが、
結果だけの表示で良いのであればもっと簡単にできると思います。m(_ _)m

No.1 回答者： tom04 回答日時： 2018/03/09 11:13

こんにちは！

質問文そのままをコードにしてみました。

Sub Sample1()
Dim lastRow As Long, myCol As Long
myCol = Selection.Column
If myCol <= Cells(1, Columns.Count).End(xlToLeft).Column - 3 Then
lastRow = Cells(Rows.Count, myCol).End(xlUp).Row
Cells(2, myCol + 3).Formula = "=Sum(" & Cells(2, myCol + 1).Address(False, False) & _
":" & Cells(2, myCol + 2).Address(False, False) & ")"
Cells(2, myCol + 3).AutoFill Destination:=Range(Cells(2, myCol + 3), Cells(lastRow, myCol + 3))
Else
MsgBox "最終列より　左3列以上を選択"
End If
End Sub

※　本来であれば細かいエラー処理が必要になると思いますが、
とりあえず1セル（1列）選択後、マクロ実行すると数式のまま結果が表示されると思います。m(_ _)m