Excel VBA オートフィルの範囲指定

質問

Excel VBA で関数を入れたセルを最下行までコピー　させたいのですが、範囲の指定がうまくできません。ごちゃごちゃ書きすぎて、よくわからなくなってしまいました。

実行してみたら、オートフィルのところでデバッグが出ました。
VBAはまだまだ初心者レベルです・・・
どこをどう直せばきちんと処理されるのか、どなたかお知恵をお貸しください。
（それと初めの定義は、Rangeで合ってるのでしょうか？）

Sub sample()

Dim MyCell1 As Range
Dim MyCell2 As Range
Dim MyCell3 As Range
Dim MyCell4 As Range
Dim MyCell5 As Range

Set MyCell1 = Cells(5, Range("4:4").Find(what:="○○", searchorder:=xlByColumns).Offset(1, 1).Column)
Set MyCell2 = Cells(5, MyCell1.Offset(0, 2).Column)
MyCell1.Select
Selection.Formula = "=$A5-" & MyCell2.Address(False, True)

Set MyCell3 = Cells(5, MyCell1.Offset(0, -1).Column)
Set MyCell4 = Cells(5, Cells(5, Columns.Count).End(xlToLeft).Column)
Set MyCell5 = MyCell1.Offset(0, 1)
MyCell5.Select
Selection.Formula = "=" & MyCell3.Address(False, True) & "-" & MyCell4.Address(False, True)

Range(MyCell1, Cells(5, MyCell1.Offset(0, 1).Column)).Select
Selection.AutoFill Destination:=Cells(Cells(5, MyCell1.Column), Cells(Cells(Rows.Count, 1).End(xlUp).Row, MyCell5.Column)), Type:=xlFillCopy

End Sub

*********************
下のような表に関数を入力して最下行までコピーさせたいです。

　｜　A　｜　B　｜　C　｜　D　｜　E　｜　F　｜　G　｜　H　｜　I　｜　J　｜　K　｜　L　｜
--------------------------------------------------------------------------
4 | code | name |　7/1　|　7/2　| ○○ |　　　　|　　　　|　code|name|　7/1　|　7/2| ○○ |
5 |10000|aaaaaa|　15　　|　20　|　35　　| 　　　 | 　　　|10001|bbbbbb|　13　|　25　|　38　|
　　　　　　　　　　　　　　　　　　　　　　　　　　((
　　　　　　　　　　　　　　　　　　　　　　　　　　　))
F5に "=$A5-$H5" と数式を入れてcodeを比較し、G5に "=$E5-$I5"と入れて数量を比較する。
F列とG列の入力されている最下行まで数式をコピーする。

※毎月日数が変わり、商品数も変わるので、A列・B列・４行目以外は全て可変。

WindowsXP Excel2003 です。
よろしくお願いいたします。

回答No.1

xls88 · ベストアンサー

AutoFillのDestinationの処を下記に訂正すれば通ります。

Destination:=Range(Cells(5, MyCell1.Column), Cells(Cells(Rows.Count, 1).End(xlUp).Row, MyCell5.Column))

こんな風に纏めることもできます。
（例1）
Dim MyCell1 As Range
Dim MyCell2 As Range
Dim MyCell3 As Range
Dim MyCell4 As Range
Dim MyCell5 As Range

Set MyCell1 = Range("4:4").Find(what:="○○", searchorder:=xlByColumns).Offset(1, 1)
Set MyCell2 = MyCell1.Offset(, 2)
MyCell1.Formula = "=$A5-" & MyCell2.Address(False, True)

Set MyCell3 = MyCell1.Offset(, -1)
Set MyCell4 = Cells(5, Columns.Count).End(xlToLeft)
Set MyCell5 = MyCell1.Offset(, 1)
MyCell5.Formula = "=" & MyCell3.Address(0, 1) & "-" & MyCell4.Address(0, 1)

Range(MyCell1, MyCell5).AutoFill _
Range(MyCell1, Cells(Cells(Rows.Count, 1).End(xlUp).Row, MyCell5.Column)), xlFillCopy

（例2）
Dim MyCell1 As Range
Dim MyCell2 As Range
Dim MyCell3 As Range
Dim MyCell4 As Range
Dim MyCell5 As Range
Dim MyRow As Long

MyRow = Cells(Rows.Count, 1).End(xlUp).Row
Set MyCell1 = Range("4:4").Find(what:="○○", searchorder:=xlByColumns).Offset(1, 1)
Set MyCell2 = MyCell1.Offset(, 2)
Range(MyCell1, Cells(MyRow, MyCell1.Column)).Formula = "=$A5-" & MyCell2.Address(0, 1)

Set MyCell3 = MyCell1.Offset(, -1)
Set MyCell4 = Cells(5, Columns.Count).End(xlToLeft)
Set MyCell5 = MyCell1.Offset(, 1)
Range(MyCell5, Cells(MyRow, MyCell5.Column)).Formula = "=" & MyCell3.Address(0, 1) & "-" & MyCell4.Address(0, 1)

お礼

ご回答ありがとうございます！

範囲指定はRangeじゃないとダメだったんですね。
早速、修正して実行してみたらできました。
でも自分のより、例に書いて頂いた方が処理が早かったので、そちらを使わせて頂きます。