VBAでセルの右下をいちばん下までドラッグして数式をコピーする方法

ご回答者様

いつも大変お世話になっております。
VBAを勉強しております。
別紙画像のCell(2,4)とCell(2,5)に数式が入っております。
この数式をA列のデータが入っている所までコピーをしたいです。
（エクセルの操作で言い換えるなら該当セルの右下が十字になったら一番下まで
ドラッグする操作）

AからC列までに毎回同じ行数のデータが入るとは限らないので毎回自動で設定したいと考えております。

iをvariantで定義するところまで分かりますがRangeの範囲指定の方法が分かりません。

下記、ソースです。

Sub 練習()

Dim i As Variant
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row

Range("D2").Select

Selection.AutoFill Destination:=Range("D2:E6"), Type:=xlFillDefault

Range("A1").Select

Next

End Sub


お忙しい所、恐れ入りますが何卒宜しくお願い致します。

No.5 ベストアンサー 回答者： tom04 回答日時： 2019/11/25 08:35

続けてお邪魔します。

再質問①について
＞兵庫県が整数に該当するのでしょうか。

lastRow = Cells(Rows.Count, "A").End(xlUp)
のように、プロパティを省略すると
lastRow = Cells(Rows.Count, "A").End(xlUp).Value
と自動的に「値」として判断してくれるみたいです。
すなわち、お示しの画像の配置だと
＞lastRow = Cells(Rows.Count, "A").End(xlUp)
だと「兵庫県」が変数に格納されます。
（ただし、Long型で宣言していると「型が違います」というエラーになるはずです）

前回のコードでは
＞lastRow = Cells(Rows.Count, "A").End(xlUp).Row
のようにプロパティを「.Row」としているので、求められるのは最終行のデータが入っている「行番号」になります。

次に一番気を付けたいことなのですが、
VBAの場合「＝」とは
算数（数学）でいう　左辺＝右辺（左辺と右辺は等しい）　という意味ではありません。
算数（数学）では「＝」は左辺と右辺が釣り合っている！というコトになりますが、
VBAの場合は
左辺に右辺を代入する！というコトになります。（←注意点※）

そしてExcel的には代入した場合、頭に「=」があると自動的に計算式と判断してくれ
仮に数式が相対参照になっている場合はセル番地に合わせて相対参照に変更してくれます。

上記のコトを利用したのがNo.２のコードになります。

質問文の
＞Range("D6").Formula = "=SUM(D3:D5)"
＞と記載がありました

は数式の前後がダブルクォーテーションに囲まれているのでD6セルに「文字列」を代入することになります。
結局D6を選択すると数式バーは　=SUM(D3:D5)　の数式が入っています。
（ダブルクォーテーションは入っていない）

No.２のコードはその後の操作（最終行までのフィル＆コピー）なので
紹介したサイトでは少し判りにくいかもしれませんね。

以上、長々と書きましたが
この程度でよろしいでしょうかね？m(_ _)m

No.4 回答者： tom04 回答日時： 2019/11/24 20:45

No.2・3です。

再質問について・・・
①　変数の宣言ですが、今回の場合はA列最終行すなわち整数を格納するので
Long型にしています。
（兵庫県という文字列を格納するのではありません）

「Variant型」はいわゆる万能型の変数になるので、エラーにはなりませんが
今回のように整数型（長整数型）と判っているのであれば
Long（長整数）型で宣言してやるのが一般的です。

変数の型としてよく使うのがLong型とString（文字列）型などほかにも色々あります。
↓のサイトに詳しく説明があります。

http://officetanaka.net/excel/vba/variable/03.htm

次に②ですが
当方がココで説明するより↓のサイトが判りやすいかもしれません。

https://programming-study.com/technology/vba-for …

もちろん質問文にあるようにオートフィルでも構いませんが
個人的にはこちらの方をよく使います。m(_ _)m

この回答へのお礼

tomo04様

いつもお世話になっております。
ご回答頂きありがとうございます。
①
Longですが調べました所、-2,147,483,648から2,147,483,647の整数と出てきましたがここでいう兵庫県が整数に該当するのでしょうか。

➁
頂いたURLのサイトを確認しましたが
Range("D6").Formula = "=SUM(D3:D5)"
と記載がありました。
Formulaというプロパティは数式を操作するプロパティですね。
（上記ソースであればD6の数式はD3からD5の合計）

tomo04様から頂いたソースですと
Range(Cells(2, "D"), Cells(lastRow, "E")).Formula = Range("D2:E2").Formula
Cell(2,D)とE列の最終行の範囲の数式はRange("D2:E2").Formulaの数式と同じ
という意味ですね。

➁につきましてはパターンとして保管します。

ご対応、ありがとうございます。

お礼日時：2019/11/24 21:54

No.3 回答者： tom04 回答日時： 2019/11/24 19:32

No.2です。

投稿後気づきました。
D・E列両方の列が対象なのですね。
（D2・E2とも前回同様下へフィル＆コピーできる数式が入っているという前提です）

前回の
＞Range(Cells(2, "E"), Cells(lastRow, "E")).Formula = Range("E2").Formula
の行を
＞Range(Cells(2, "D"), Cells(lastRow, "E")).Formula = Range("D2:E2").Formula
に変更してください。

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

この回答へのお礼

tomo04様

いつもお世話になっております。
表題の件、頂いたソースで動きました。
ありがとうございます。
勉強のために教えて頂きたく存じます。

１．Dim lastRow As Longとされていますが
おそらくこれは最終行が整数であることを定義しているかと存じます。
今回ですと兵庫県ですので数字ではないかと存じますが何故Longでも
マクロが走るのでしょうか。
（最初は念のためにVariantで定義しました）
lastRowがA列の一番下のデータを指していることはソースを見て理解しました。


２．Range(Cells(2, "D"), Cells(lastRow, "E")).Formula
上記内容ですがCells(2, "D")と(lastRow, "E")の最終行をドラッグしているイメージかと存じます。
ここで出てきているFormulaですが調べると数式と出てきましたが具体的にどのような意味でしょうか。

宜しくお願い致します。

お礼日時：2019/11/24 19:54

No.2 回答者： tom04 回答日時： 2019/11/24 19:22

こんばんは！

E2セルにはそのまま下へフィル＆コピーできる数式が入っているのですよね。
そして、E列の最終行（A列で最終行を取得）まで数式をフィル＆コピーしたい！
という解釈で・・・
一例です。

Sub Sample1()
　Dim lastRow As Long
　　lastRow = Cells(Rows.Count, "A").End(xlUp).Row
　　　Range(Cells(2, "E"), Cells(lastRow, "E")).Formula = Range("E2").Formula
End Sub

ではどうでしょうか。m(_ _)m

No.1 回答者： GooUserラック 回答日時： 2019/11/24 19:01

一応確認ですが、A列が途中抜けている所が有った場合は次のどの処理になりますか？

① 抜けている所にもコピーする。
② 抜けている所は飛ばしてコピーする。
③ 抜けている手前までコピーして終了する。

この回答へのお礼

GooUserラック 様

いつもお世話になっております。
下記の内容を想定しております。

③ 抜けている手前までコピーして終了する。


宜しくお願い致します。

お礼日時：2019/11/24 19:13