[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『VlookUpとオートフィル』（みず）
こんばんは。
データベースから
VLOOKUPで金額、状態、ステータスと3項目を転記しています。
マクロの記録を使ってプログラムを作って使用しています。
「一覧 - 購買ｵｰﾀﾞ明細」と言うファイルは毎回名前が変わるのですが
プログラムを走らせるとファイル選択のダイアログが出てくるので
取りあえず3回転記元ファイルを選択し4回目をキャンセルする事で
何とか使えています。
これを1回選択するだけで、最後ま処理できるようにしたいのですが
どなたかご教授お願いできますでしょうか？
Sub VlookUpとオートフィル()
'最終行の取得
LstRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("FT2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-171],'一覧 - 購買ｵｰﾀﾞ明細'!C5:C13,9,0)"
Range("FU2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-172],'一覧 - 購買ｵｰﾀﾞ明細'!C5:C17,13,0)"
Range("FV2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-173],'一覧 - 購買ｵｰﾀﾞ明細'!C5:C22,18,0)"
'オートフィル
Range("FT2:FV2").AutoFill Destination:=Range("FT2:FV" & LstRow)
'#N/N 削除
ActiveSheet.Range("$A$1:$FV$340").AutoFilter Field:=177, Criteria1:="#N/A"
Columns("FT:FV").Select
Application.CutCopyMode = False
Selection.ClearContents
ActiveSheet.Range("$A$1:$FV$340").AutoFilter Field:=177
'数式削除
Columns("FT:FV").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
'最終行の取得の次に
Dim strPath As String
With Application.FileDialog(msoFileDialogFilePicker)
.Show strPath = .SelectedItems(1) End With
MsgBox strPath
を入れて
VLOOKUPの引数を変更して走らせてみたのですが
キャンセルするまで何回もフォイルを聞いてくし
転記がうまくいきません。（引数の設定が悪いのかもしれませんが）
'#N/N 削除の所も無駄というか
もっとスマートな処理があれば教えて頂けると助かります。
＜ 使用 Excel：Office365、使用 OS：Windows10 ＞
Sub VlookUpとオートフィル整理()
Dim LstRow As Long
With ActiveSheet
'最終行の取得
LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("FT2").Formula = "=VLOOKUP(E2,'一覧 - 購買ｵｰﾀﾞ明細'!E2,$E:$M,9,0)"
.Range("FU2").Formula = "=VLOOKUP(E2,'一覧 - 購買ｵｰﾀﾞ明細'!E2,$E:$Q,13,0)"
.Range("FV2").Formula = "=VLOOKUP(E2,'一覧 - 購買ｵｰﾀﾞ明細'!E2,$E:$V,18,0)"
'オートフィル
.Range("FT2:FV2").AutoFill Destination:=.Range("FT2:FV" & LstRow)
'#N/N 削除
.Range("$A$1:$FV$340").AutoFilter Field:=177, Criteria1:="#N/A"
.Columns("FT:FV").ClearContents
.AutoFilterMode = False
'数式削除
With .Columns("FT:FV")
.Value = .Value
End With
End With
End Sub
おそらく、質問の答えとしては「一覧 - 購買ｵｰﾀﾞ明細」というシートが見つからず、それはどこなのさ？と聞かれている状態なので、本当にシート名がまちがってないか再確認が必要という感じじゃないでしょうか？
とりあえず、半角スペース含めて一致しないとダメなので、その辺を確認されてみては如何でしょうか？
（もこな２） 2020/02/03(月) 06:13
ファイルを選択をさせた後
VLOOKUPのある行でまたファイルを聞いてきます。
なぜなんでしょうか？
取得したファイルのパスの引数の使い方が悪いのでしょうか？
Sub VlookUpとオートフィル整理()
Dim LstRow As Long
Dim dlg As FileDialog
Dim strPath As String
With Application.FileDialog(msoFileDialogFilePicker)
.Show strPath = .SelectedItems(1) MsgBox strPath End With
With ActiveSheet
'最終行の取得
LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
MsgBox LstRow
.Range("FT2").Formula = "=VLOOKUP(E2,'strPath'!$E:$M,9,0)"
.Range("FU2").Formula = "=VLOOKUP(E2,'strPath'!$E:$Q,13,0)"
.Range("FV2").Formula = "=VLOOKUP(E2,'strPath'!$E:$V,18,0)"
'オートフィル
.Range("FT2:FV2").AutoFill Destination:=.Range("FT2:FV" & LstRow)
'#N/N 削除
.Range("$A$1:$FV$340").AutoFilter Field:=177, Criteria1:="#N/A"
.Columns("FT:FV").ClearContents
.AutoFilterMode = False
'数式削除
With .Columns("FT:FV")
.Value = .Value
End With
End With
End Sub
（みず） 2020/02/03(月) 10:11
MsgBox strPath で表示されたものは何ですか？ 説明できますか？ シート情報も入っているんですか？
MsgBox strPath の後に、 MsgBox "=VLOOKUP(E2,'strPath'!$E:$M,9,0)" として、その内容を確認して下さい。 どうなっていますか？
""の中に入れた変数名は、 変数のつもりでも、単なるstrPathという文字列だと思いますよ。
また、手作業で別のブックの表をVLOOKUPで参照するとき、 どのような式になるか、確認していますか？
更に言えば、手を入れて頂いたコードをご覧になって、 どこが悪かったのか、把握していますか？
以上の事柄は、かなり重要な考え方で、それを学ぶ良いチャンスです。 ご自分で確認することが大切です。
（γ） 2020/02/03(月) 11:42
検索は VLOOKUP 変数、VLOOKUP 範囲指定、VLOOKUP 式
VLOOKUP ファイル名 範囲指定、など
＞MsgBox "=VLOOKUP(E2,'strPath'!$E:$M,9,0)"
は""で囲っているので文字として認識していると思います。
=VLOOKUP(E2,'strPath'!$E:$M,9,0)と表示しています。
'strPath'の表記が正しいか（接続記号はあっているかなど）は
MsgBox "=VLOOKUP(E2,'strPath'!$E:$M,9,0)"ではわからない様に思います。
フルパスからファイル名のみをとりだせる様になりましたが
取り出せても記述の仕方がわからないので先に進めない状態です。
（みず） 2020/02/03(月) 21:19
手作業で、一例を作って見て下さい。
どんな式になっていますか？それをこちらにアップして下さい。
それが分からないということはないですよね。
VBAというのは、なんらかの動作を自動化するものですが、
その「なんらか」というところは、しっかり認識しないと、前に進みません。
（γ） 2020/02/03(月) 21:31
'最終行の取得
LstRow = Cells(Rows.Count, 1).End(xlUp).Row
Range("FT2").Select
Application.CutCopyMode = False
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-171],'オーダー明細'!C5:C13,9,0)"
'オートフィル
Range("FT2:FV2").AutoFill Destination:=Range("FT2:FV" & LstRow)
End Sub
です。
"見積一覧"というファイルに"オーダー明細"ファイルの金額を転記します。
検索値は"見積一覧"の"E2"
範囲は"オーダー明細"のC5からC13
列番号は9
転記先のセルは"FT2"です。
"オーダー明細"と言うファイル名は毎回変わるので
ファイルを開くダイアログで転記元のファイルを選択して
処理したいのです。
（みず） 2020/02/04(火) 01:09
↑ができたら、必要な命令はどの部分なのか【ステップ実行】してしらべてみて、無駄な部分を削除するなど整形してみる。
↑ができたらR1C1参照形式を【A1参照形式】で記述してみる。
↑ができたら、対象シートを変えるにはどの部分を変えればよいか【考えてみる】
↑ができたら、実際にコードを変えてみる
↑ができたら、ステップ実行して問題ないか試してみる
↑ができたら、エラー値の削除や、数式→値へ変換 の部分と組み合わせる
としてみてはどうですか？
（もこな２） 2020/02/04(火) 06:46
そのマクロを使っていますが
ファイル名が毎回違うので、
毎回プログラムを書き換えるのも手間がかかるので
そのまま使っても、対象のファイルがないと言う事で
ダイアログが開きくので、そこで希望のファイルを選択して使用しています
。
対象のファイルをかえるのは、どこを変えればよいかはわかります。
先に書きましたが毎回そこを書き換えるのは手間です。
自分のみが行う作業ならそれでも良いですが
プログラムをさわる事に抵抗がある人に
どこの、どの場所をどう変えるかというのを
理解してもらうのは大変です。
（みず） 2020/02/04(火) 08:06
要するに FT2にセットする数式としては >=VLOOKUP(RC[-171],'一覧 - 購買ｵｰﾀﾞ明細'!C5:C13,9,0) は（ブック名の指定がないので）不完全だということです。
まずは正しく機能する数式を得る（※）事です。 ※ フォルダパス・ブック名・シート名・セル範囲のすべてが必要です。
〜〜〜〜〜〜 γさんへ
"=VLOOKUP(RC[-171],'オーダー明細'!C5:C13,9,0)"
↑は R1C1形式の表現で問題ありません。A1形式と混在してはいません。
ちょっと気になったので。
（チオチモリン） 2020/02/04(火) 10:03
| 自分のみが行う作業ならそれでも良いですが | プログラムをさわる事に抵抗がある人に | どこの、どの場所をどう変えるかというのを | 理解してもらうのは大変です。
他人のせいにされるなら、今までどおり手作業でやったほうがよいと思います。 パス名だけ取得しても、シート名を指定させたり、セル範囲を指定したり、 それは結局、手作業でやるのと殆ど変わりありません。 今まで手作業でやってきたというなら、それを継続した方がよいかもしれません。
パス名を式に反映するコードも書きましたが、やめましょう。 今までとおり、慣れた手作業でやったほうがよいでしょう。 私はここまでとさせていただきます。
（γ） 2020/02/04(火) 10:23
＞"見積一覧"というファイルに"オーダー明細"ファイルの金額を転記します。 ＞検索値は"見積一覧"の"E2" ＞範囲は"オーダー明細"のC5からC13 ＞列番号は9 ＞転記先のセルは"FT2"です。 ＞"オーダー明細"と言うファイル名は毎回変わるので ＞ファイルを開くダイアログで転記元のファイルを選択し処理したいのです。
やりたいことは大体わかりましたが、仕様はキッチリ書いてください。
ブック名とシート名が分からないと的確に処理できません。
上の記述だと、 １．ブック名は "見積一覧" と "オーダー明細" ですね？ 拡張子は何ですか？（xlsm,xlsx?)
２．シート名は(紛らわしくて、ホントと思うんですが） 「見積一覧」 「オーダー明細」
なのですか？
あと、当初こんな記述がありましたが、「ダ」で止まっていて、混乱の元ですよ。 ↓ ＞「一覧 - 購買ｵｰﾀﾞ明細'!C5:C13,9,0)"
（半平太） 2020/02/04(火) 10:53
＞拡張子は何ですか？（xlsm,xlsx?)
すみません。
拡張子はStrPathに入ってくるので不要でした。m(__)m
その質問は無視してください。
（半平太） 2020/02/04(火) 11:39
不完全な部分の解説ありがとうございました。
少し先に進めました。
パス名やファイル名を式に反映させようと
色々試しているところです。
Sub VlookUpとオートフィル()
Dim LstRow As Long
Dim OpenFileName As String
Dim FileName As String
Dim strFormula As String
Dim strVlook As String
'// ダイアログでファイルを選択
'With Application.FileDialog(msoFileDialogFilePicker)
' .Show
' OpenFileName = .SelectedItems(1)
' MsgBox OpenFileName '// 検証の為表示して確認
'// ダイアログでファイルを選択した状態を仮定
OpenFileName = "C:\Users\abc3\OneDrive\ドキュメント\VBA 練習\VBA プログラム\データ 191227-170935.xlsx"
MsgBox "OpenFileName" & vbNewLine & OpenFileName '// 検証の為表示して確認
FileName = Dir(OpenFileName)
MsgBox "FileName" & vbNewLine & FileName '// 検証の為表示して確認
Path = Replace(OpenFileName, FileName, "")
MsgBox "Path" & vbNewLine & Path '// 検証の為表示して確認
strFormula = Path & FileName
MsgBox "strFormula" & vbNewLine & strFormula '// 検証の為表示して確認
strVlook = ("E2,'" & Path & "[" & FileName & "]" & "Sheet(1)'!$E:$M,9,0")
MsgBox "strVlook" & vbNewLine & strVlook '// 検証の為表示して確認
'End With
With ActiveSheet
'最終行の取得
LstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
'.Range("FT2").Formula = "=VLOOKUP(E2,'C:\Users\abc3\OneDrive\ドキュメント\VBA 練習\VBA プログラム\[データ 191227-170935]Sheet(1)'!$E:$M,9,0)"
'// 結果：正常に処理
'.Range("FT2").Formula = "=VLOOKUP(strVlook)"
'// 結果：実行時エラー、アプリケーション定義またはオブジェクト定義エラー
'.Range("FT2").Formula = "=VLOOKUP(E2,'& Path & "[" & FileName & "]" & Sheet(1)'!$E:$M,9,0)"
'// 結果：コンパイルエラー、構文エラー
'.Range("FT2").Formula = "=VLOOKUP(E2,'& Path &[データ 191227-170935]Sheet(1)'!$E:$M,9,0)"
'// 結果：ダイアログボックスが出てファイルの選択を促す
'// 指定したパスにファイルがない？
'.Range("FT2").Formula = "=VLOOKUP(E2,'C:\Users\abc3\OneDrive\ドキュメント\VBA 練習\VBA プログラム\ & "[" & FileName & "]" & Sheet(1)'!$E:$M,9,0)"
'// 結果：コンパイルエラー、修正候補、ステートメントの最後、[" & FileName & "]反転
End Sub
（みず） 2020/02/04(火) 23:55
ファイル名など実ファイルや検証用のファイルなど
混同してしまい、ややこしくなりました。
保存先変更や違いファイルでの検証しながらで一貫性が無いようになりましたが
ご容赦下さい。
（みず） 2020/02/05(水) 00:01
'.Range("FT2").Formula = "=VLOOKUP(E2,'& Path & "[" & FileName & "]" & Sheet(1)'!$E:$M,9,0)"
"=VLOOKUP(E2,'" & Path & "[" & Filename & "]" & "Sheet(1)'!$E:$M,9,0)"
※ ブックの拡張子の表示が気になりますが。
（チオチモリン） 2020/02/05(水) 00:58
＞'.Range("FT2").Formula = "=VLOOKUP(strVlook)"
↓ 正
.Range("FT2").Formula = "=VLOOKUP(" & strVlook & ")"
なお、下の様にエラー処理も入れてしまえば、「#N/A 削除」に掛かるコードは不要化します。
.Range("FT2").Formula = "=IFERROR(VLOOKUP(" & strVlook & "),"""")"
（半平太） 2020/02/05(水) 08:18
半平太様
エラー処理も教えて頂き有難うございました。
式も入れ替えましたら問題なく処理できました。
チオチモリン様
半平太様
式の””で囲むという部分を理解して応用できる様頑張ります。
有難うございました。
（みず） 2020/02/05(水) 10:09
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.