Excelのマクロを使ってのオートフィル

質問

Excelのマクロを使ってのオートフィルについて教えて下さい。
類似の質問があったのですが、決まったセルからのものが多くわかりませんでした。
マクロはまだ始めたばかりです。
やりたいことはExcelの2つのシートに別のcsvファイルの数字を入れることです。
ExcelのSheet1のD１セルに日付を表す数字が入っています。
Sheet2と3には2行目に月の日付がずらりと並んでいます。
1列目には記録番号が入っています。
それぞれのシートにSheet1のD１セルと同じ日付の列を見つけて、
その下の行から1列目の記録番号を元に検索したcsvファイルのデータを引っ張ってきたいのです。

　　Sheets("Sheet1").Select
Cells(3, (Sheets("Sheet2").Range("D1")).Select

と最初に入れるセルを探しても違う列に飛んでしまったりします。
又、下のセルまで数式もコピーできずに止まったままです。

VBAまでは手が出せずに悩んでいます。どうか教えて下さい。よろしくお願いします。

回答No.4

xls88 · ベストアンサー

ANo.2で提示したコードは一例です。
Activecellの1行上の数式をコピーしています。
そのまま使っても上手くいかないのは当然です。

>ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],tanto.csv!C1:C2,2,0)"
”1列目の記録番号を元に検索”ということですからActiveCellの位置によって、列の相対位置を計算しなければなりません。
”1列目”がA列なら
With ActiveCell
　　.FormulaR1C1 = "=VLOOKUP(RC[-" & .Column - 1 & "],tanto.csv!C1:C2,2,0)"
End With
で良いと思います。
.Column - 1 の 1 は、1列目の記録番号がどの列にあるかによって変わってきます。

下方にオートフィル入力するなら
With ActiveCell
　　.Resize(3, 1).FormulaR1C1 = "=VLOOKUP(RC[-" & .Column - 1 & "],tanto.csv!C1:C2,2,0)"
End With
といった感じになります。
Resize(行数, 列数)です。
行数を決定しなければなりません。何か手掛かりはありますか？

A列のデータ最終行から計算するなら
With ActiveCell
　　.Resize(Cells(Rows.Count, "A").End(xlUp).row - .Cells.row + 1, 1).FormulaR1C1 = _
　　　　"=VLOOKUP(RC[-" & .Column - 1 & "],tanto.csv!C1:C2,2,0)"
End With
といった感じになります。

お礼

xls88さん、どうもありがとうございました。
不十分な説明にも関わらず細かい解説をいただき、勉強になりました。
今後もつまづきながらになると思いますので、機会がありましたら今後共どうかよろしくお願い致します。
本当にどうもありがとうございました。

補足

xls88さん、度々どうもありがとうございます。
記録番号からの検索というのはわかりました。
でもおっしゃる通り、最終行が日々変わっていることに気付かず行き詰りました。
下記のmitarashiさんへの補足でも入れたのですが、
ExcelのSheet2のA列にある記録番号（取引先コード）の最終行を毎日確認して決めてしまうか、
csvファイルの最終行の見つけ方があるならばその方法で行いたいと考えています。
知識がないため今思いつくのは最終行を日付と同じようにどこかのセルに数字で入力する、位なのですが・・・。
お時間のできた際に又アドバイスいただけたらうれしいです。
どうかよろしくお願い致します。

回答No.3

mitarashi

#1です。
補足していただいた内容からすると、下記の内容で良い気がしますが、#2さんへの補足＆追加質問とは合致しませんので相変わらず全容が良く分かりません。
Sub test()
'転記先ファイル（そこにマクロも記録）のSheet1から実行
Range("D1").Select
'オプション／全般で、R1C1参照方式にしておいて記録し、いじった
Application.Goto Reference:="Sheet2!R3C" & Selection.Value
Windows("ＣＳＶファイル名.csv").Activate
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("転記先ファイル名.xls").Activate
ActiveSheet.Paste Link:=True

Sheets("Sheet1").Select
Range("D1").Select
Application.Goto Reference:="Sheet3!R3C" & Selection.Value
Windows("ＣＳＶファイル名.csv").Activate
Range("B2").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Windows("転記先ファイル名.xls").Activate
ActiveSheet.Paste Link:=True
End Sub

=VLOOKUP(RC[-17],tanto.csv!C1:C2,2,0)
の意味は？やはり、転記先ブックのSheet2には、A列に0001,0002,が並んでいるのでしょうか？これはヒットしないケースもあるのですか

お礼

mitarashiさん、どうもありがとうございました。
他力本願ではいけないと格闘した結果、最終行を見つけて動かすことができました。
今後もつまづきながらになると思いますので、機会がありましたら今後共どうかよろしくお願い致します。
本当にどうもありがとうございました。

補足

mitarashiさん、度々どうもありがとうございます。
元となるファイルに多少変更が入ってしまったため、
自分で実際に動かして確認するのに時間が掛かってしまいました。
説明不足なのもご指摘の通りで申し訳ありません。

＞=VLOOKUP(RC[-17],tanto.csv!C1:C2,2,0)
の意味は？
今は=VLOOKUP(RC[-17],tanto.csv!A2:C2,3,0)なのですが、
tanto.csvのA列の記録番号を元にデータを探しています。
Excel、csvファイルは変更が入ってしまって現在下記の内容になっています。

Sheet1
.............A.............B.............C.............D.............E
....1.......................................................................23...............

E1セルに日付を表す数字が入っています。今日だったら23と入力します。
（※Ｄ1セルからＥ1セルに変更になってしまいました。）
Sheet2
...................A...................B...................C...................D...................E
...1........................................................................
...2......記録番号........空欄.................. 1...................2...................3....................4～31まで

このシートの説明が特に不十分でした。
１行目にはタイトルのみ。
２行目にはＡ2セルに「記録番号」という表示、Ｂ2セルは空欄になっていて通常非表示の状態です。
そしてＣ2セルから日付を表す1から31までの数字がそれぞれの列に入っています。
やりたいことはSheet1のＥ1セルに入った数字を見て、該当する列の３行目からcsvファイルのデータ（数字）を貼り付けたいのです。
csvファイルからVLOOKUPで探すのは記録番号でA列に入っています。

csvファイルのデータの配置も少し変わりました。
.............A................B................C................D................E
.1.....タイトル行
.2.....タイトル行
.3.....タイトル行
.4.....記録番号.....項目1.....項目2.....項目3.....項目4
.5.......0001..............381..........475..........398..........208...
.6.......0002..............275..........572..........279..........349...
.7.......0003..............579..........256..........167..........648...
.8.......0004..............385..........249..........876..........387...
.9.......0005..............779..........469..........237..........279...

最初は該当する列をまず見つける段階でズレが生じていました。
今は該当する列にきちんと数字を入れることができるようになったのですが、
毎日必ずしもデータの行数が同じでないことを知りました。
記録番号は厳密には取引先コードで、外部にわかりにくいように記録番号という表示にしてあります。
これは重複はありません。
記録番号（取引先コード）は日々増減し、増えた分はExcelのSheet2でも最終行に追加し、
減った分は月が替わった際に削除するかとりあえず0という数字を入れていくことになっています。
実際にはマクロの中で「#N/A」を「0」に置き換える動作を行って記録し、実行しています。
今行き詰ってしまっているのは、最終行をどうしたらよいかということです。
ExcelのSheet2のA列にある記録番号（取引先コード）の最終行を見るのが一番確実だとは思うのですが、
Sheet2の最終行の下には合計や他の計算式が入った行が全部で５行分入っています。
csvファイルの最終行の見つけ方があれば知りたいです。
上記csvファイルの項目1と項目2も2つのcsvファイルを元に作成したもので、そこでもマクロを使っているからです。
まだまだ説明不足かも知れないため恐縮ですが、もしご迷惑でなければ又アドバイスいただけたらうれしいです。
どうかよろしくお願い致します。

回答No.2

xls88

>それぞれのシートにSheet1のD１セルと同じ日付の列を見つけて、
>その下の行から1列目の記録番号を元に検索したcsvファイルのデータを引っ張ってきたいのです。
>
>Sheets("Sheet1").Select
>Cells(3, (Sheets("Sheet2").Range("D1")).Select

Cellsプロパティに対してシート名が省略されている場合
ActiveSheet.Cells(3, (Sheets("Sheet2").Range("D1")).Select
という意味になります。
直前に
Sheets("Sheet1").Select
しているので
Sheets("Sheet1").Cells(3, (Sheets("Sheet2").Range("D1")).Select
ということになります。

やりたいことは
Sheets("Sheet2").Select
Cells(3, Sheets("Sheet1").Range("D1")).Select
あるいは
Sheets("Sheet1").Select
Sheets("Sheet2").Cells(3, Sheets("Sheet1").Range("D1")).Select
ではないでしょうか？

Cells(行, 列)ですから
Sheets("Sheet1").Range("D1")の値が、1とか2のような整数ならOKです。
9/20 のように、いわゆる日付データですとエラーになります。

Sheets("Sheet1").Range("D1")
の値が、1とした場合
Sheets("Sheet2").Select
Cells(3, Sheets("Sheet1").Range("D1")).Select
は
Sheets("Sheet2").Cells(3, 1).Select
ということですから
Sheet2のセルA3がSelectされます。

>1列目には記録番号が入っています。
ということですから、１列ずれることになります。
対処するには
Cells(3, Sheets("Sheet1").Range("D1") + 1).Select
というように、＋１補正すれば
Sheets("Sheet2").Cells(3, 2).Select
となり
Sheet2のセルB3がSelectされます。

>下のセルまで数式もコピーできずに止まったままです。
元の数式はどこにありますか？
そして、どこまでコピーするのですか？
例えば
Sheets("Sheet2").Select
Cells(2, Sheets("Sheet1").Range("D1")).Select
Selection.FormulaR1C1 = Selection.Offset(-1).FormulaR1C1
セル
Cells(2, Sheets("Sheet1").Range("D1"))
に、
1行上(Selection.Offset(-1, 0))のセルの数式(FormulaR1C1)がコピーされます。

補足

xls88さん、ありがとうございます。
Sheet2の思っていたセルをきちんと見つけて入るようになりました。

数式はcsvファイルを参照する内容なのですが、
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-17],tanto.csv!C1:C2,2,0)"
Selection.FormulaR1C1 = Selection.Offset(-1).FormulaR1C1を
その下に入れると日付の数字をコピーしてしまいます。

VLOOKUP関数は使えないのでしょうか。
この作業をしている時にはこの参照をさせるために、tanto.csvファイルは開いた状態になっています。

あまりにご丁寧な内容だったので引き続き質問するのも恐縮なのですが、
できましたらどうかよろしくお願い致します。

回答No.1

mitarashi

分からない事が多すぎて、回答できません。
Sheet1
.............A...............B...............C..................D
..1......................................................2008/9/18
は良いとして、
質問通りなら、
Sheet2
...................A.......................B.......................C......................D......................E.......................F
..1
..2.............0001.....2008/9/16.....2008/9/17.....2008/9/18.....2008/9/19....2008/9/20
ですが、実際は
...................A.......................B.......................C......................D......................E.......................F
..1.............0001................0002.................0003.................0004...............0005.................0006
..2...2008/9/16.....2008/9/17.....2008/9/18.....2008/9/19....2008/9/20.....2008/9/21
こうなっているのでしょうか？
1)Sheet1!D1の日付を、Sheet2! 2行目で検索
2)上記の例では、見つかった行のSheet2!C2の値0003に対応する値を、ひとつの決まったCSVファイルから引っ張ってきて、Sheet2!C3以下に貼り付ける？
その場合のCSVのデータの配置はどうなっているの？
オートフィルはどこで出番がある？
と、十分な情報をいただけないと、回答できません。

補足

mitarashiさん、ありがとうございます。
説明不足で申し訳ありません。
Sheet1
.............A.............B.............C.............D.............E
....1....................................................19...............
日付は数字が入っています。形式は数値になっています。
Sheet2
.............A.............B.............C.............D.............E
...1........................................................................
...2.........1.............2.............3..............4.............5....～31まで
Sheet1のD1セルだけは毎日変更することになっています。
今日だったら19と入力します。
csvファイルはその日1日分のデータが入っているので、
Sheet2の19の数字が入った列に縦にデータを貼り付けたいのです。
csvファイルのデータの配置は
.............A.............B.............C.............D.............E
.1.....項目1.....項目2.....項目3.....項目4.....項目5
.2.......0001........381..........475..........398..........208...
.3.......0002........275..........572..........279..........349...
.4.......0003........579..........256..........167..........648...
.5.......0004........385..........249..........876..........387...
.6.......0005........779..........469..........237..........279...
Sheet2には項目1のデータ、Sheet3には項目2のデータを貼り付けることになっています。
オートフィルは該当する日付の数値の列を見つけた後、
csvファイルからデータを引っ張ってくる際に必要な数式を3行目に入力します。
それを下の方までオートフィルで入力できたらと考えています。
但しデータの数は日によって多少増減するため、最終行を毎日Sheet2とSheet3で足りなくならないようにしています。
マクロでは一度作業をした動作を実行してくれるので簡単に出来そうなのですが、
この最終行についてはVBAか何か他の方法を使わないとダメなのかなと悩んでいます。
不十分な説明にも関わらずご質問いただき、ありがとうございます。
どうかよろしくお願い致します。