エクセルマクロでＡ欄に値がある行までのＣ列の範囲を指定したい。?

質問

エクセルマクロでＡ欄に値がある行までのＣ列の範囲を指定したい。?

●エクセルマクロのＶＢＡで次のように記述しています。

Range("C2").FormulaR1C1 = "=VLOOKUP(LEFT(RC[-2],4),C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range("C2:C148"), Type:=xlFillDefault

A列に文字が入っている最終行は A148なので、C2: C148 の範囲にオートフィルで貼り付けるように
しています。 しかし、A列に文字が入っている最終行は、変動します。

なお、貼り付ける範囲はいつでも C2から始まります。
A列に文字が入っている最終行が A200であれば、C2: C200の範囲、
A列に文字が入っている最終行が A321であれば、C2: C321の範囲といったように、
C2の値を貼り付ける範囲を設定したいと思っています。

●そこで、次のように記入してみました。

With Worksheets(2)
Dim lRow As Long,
lRow = .Cells(Rows.Count, 1).End(xlUp).Row

Range("C1").Formula = "名称"
Range("C2").FormulaR1C1 = "=VLOOKUP(LEFT(RC[-2],4),C[-2]:C[-1],2,FALSE)"
Range("C2").Select
Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(lRow, 3)), Type:=xlFillDefault

変数を使って最終行を指定しても上手くいきませんでした。
（C2：C150）や（C2:C160）といったように「C2」のセルを「C2のセル」から「A列に文字が
入っている行のC列」まで貼り付けたいと思っています。

どのようにＶＢＡを記述したら良いのか教えていただけないでしょうか。

回答No.3

Wendy02 · ベストアンサー

>変数を使って最終行を指定しても上手くいきませんでした。
ケアレスミスですね。

>Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(lRow, 3)), Type:=xlFillDefault
Selection.AutoFill Destination:=Range(Cells(2, 2), Cells(lRow, 3)), Type:=xlFillDefault
ということです。
しかし、

>Range("C2").Select
>Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(lRow, 3)), Type:=xlFillDefault

は、Select が不要です。入門から初級に移行するには、まず、Select --> Selection は特別なことがない限りは、使わないようにしてください。ひとつにまとめて、

Range("C2").AutoFill Destination:=Range(Cells(2, 2), Cells(lRow, 3)), Type:=xlFillDefault

また、VBAなどのVB6系では、

Dim lRow As Long,　←プロシージャの始めに宣言します。それに、これは、右側の変数を意図的に消したものでしょうけれども、カンマが残っています。カンマは不要。

>With Worksheets(2)
(2)インデックスは意図的に行わない限りは、あまり使わないことです。インデックスは順序のことですから、移動したら、マクロでは分からなくなりますから、明示的に名称を入れます。

With スーテトメントを使うなら、
Range("C1").Formula = "名称"　なども、すべて、それに従わなくてはなりません。それが面倒なら、
Worksheets(2).Select で済ませてください。しかし、標準モジュールでないといけません。

なお、AutoFill メソッドは、記録マクロに存在するもので、あまりVBAではあまり使いません。理由は、物理的な操作だからです。

'//
'私なら、このように書いています。
Sub Test2()
With Worksheets("Sheet2")
　.Range("C1").Value = "名称"　'←Formula プロパティではなく、Value プロパティ
　With .Range("C2", .Cells(Rows.Count, 1).End(xlUp).Offset(, 2))
　　　'FormulaR1C1 は不要, ただし、FormulaLocal が必要になることがあります。
　　　.Formula = "=VLOOKUP(LEFT(RC[-2],4),C[-2]:C[-1],2,FALSE)"
　End With
End With
End Sub

お礼

行いたい操作ができました。

「Select --> Selection」を使わなくて済んだり、
AutoFillを使わなくても同じ操作ができたり、楽しいと思いました。

ＶＢＡの初心者で主に「マクロの記録」でやりたい操作を行い、
Visual Basic で記録されたマクロの中で不必要だと思ったものを消していくという
操作でマクロを記述していました。

勉強になりました。

ありがとうございます。

回答No.6

tom04

ほんとぉ～！っに何度もごめんなさい。

前回のコードで行がダブっていました。

Sub test()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(2, 3).AutoFill Destination:=Range(Cells(2, 3), Cells(lRow, 3))
End Sub

に訂正してください。m(__)m

回答No.5

tom04

No.2・4です！
何度もごめんなさい。

以前はまったく的外れでしたので、もう一度やってみました。
「C2セルに数式が入っていて、それをA列に入力がある行までオートフィルで下へコピーする。」として、

こんな感じでよいのですかね？
ごく単純に・・・

Sub test()
Dim lRow As Long
lRow = Cells(Rows.Count, 1).End(xlUp).Row
lRow = Cells(Rows.Count, 1).End(xlUp).Row
Cells(2, 3).AutoFill Destination:=Range(Cells(2, 3), Cells(lRow, 3))
End Sub

これを　Range("C2").Select　以降に変更してみてください。

以上、参考になればよいのですが
今回も外していたらごめんなさいね。m(__)m

回答No.4

tom04

No.2です！
前回は大きな勘違いをしていました。

無視してください。
どうも失礼しました。m(__)m

回答No.2

tom04

こんばんは！
もしかしてこんなことですかね？

Sub test1()
Dim i As Long
For i = 2 To Cells(Rows.Count, 1).End(xlUp).Row
Cells(i, 3) = Cells(2, 3)
Next i
End Sub

前半のVLOOKUPの方は無視しています。

外していたらごめんなさいね。m(__)m

回答No.1

kmetu

Selection.AutoFill Destination:=Range(Cells(3, 2), Cells(lRow, 3)),
を
Selection.AutoFill Destination:=Range(Cells(2,3), Cells(lRow, 3)),
に変更してみてください。

Cells(3, 2),じゃなくてCells(2, 3),

お礼

ありがとうございました。
思っていた結果が得られました。