VBAでVlookupを使って現場検索を行う方法

2023/08/01 10:24

このQ&Aのポイント

VBAを使用して、現場検索登録シートでVlookupを使って一覧シートからデータを表示する方法について説明します。
Vlookupを使用して表示されるデータが一部正常に検索されない現象が発生しています。原因を特定する方法についても知りたいです。
Vlookupのマクロを使用してデータを検索していますが、一部のデータの表示が正しくありません。解決策や改善方法を教えてください。

ベストアンサー

VBAでVlookupを使って

2005/06/05 20:21

いつもお世話になっています。 VBAで現場検索登録シートと言うところに一覧シートからVlookupを使って表示させようと思い作りました。エラーも何もでません。ただ、表示されるものがちゃんと検索されるものとまったくされないものがあります。 Vlookupはマクロの記録から作ったものです。原因がわかりません。よろしくお願い致します。 '検索ボタン Private Sub CommandButton6_Click() '送り方 Range("C4:D4").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,22,FALSE)" Selection.Value = Selection.Value '封筒 Range("C5:D5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,23,FALSE)" Selection.Value = Selection.Value '得意先名ふりがな Range("C6:F6").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,6,FALSE)" Selection.Value = Selection.Value '得意先名 Range("C7:F8").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE)" Selection.Value = Selection.Value '現場名ふりがな Range("C9:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,9,FALSE)" Selection.Value = Selection.Value '現場名 Range("C10:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,7,FALSE)" Selection.Value = Selection.Value End Sub エラーも何もなく、ちゃんと動くことは動きます。検索結果が、検索コードによって違うのです。よろしくお願い致します

cocoku
お礼率51% (34/66)

その他（プログラミング・開発）
回答数2
ありがとう数2

みんなの回答 （2）
専門家の回答

質問者が選んだベストアンサー

ベストアンサー

papayuka
ベストアンサー率45% (1388/3066)

2005/06/09 00:22 回答No.2

回答が遅くなりました。 > 一覧表が昇順になっていないからだとわかりましたが最後にFALSEを指定してますので、ソート順は関係無いと思いますけど、、、 > このエラーがでます。「#REF!」 "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,55,FALSE)" でC54なのに55を指定してますから#REF!が出るのは当然でしょう。 > また、検索結果が空白の場合、「０」が表示されるのをこの書き方で行くのなら、 ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE)" を ActiveCell.FormulaR1C1 = _ 　　"=IF(VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE)="""", """"," & _ 　　　　"VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE))" に変えるとか。

質問者

お礼 2005/06/12 12:47

ありがとうごさいます。単純なミスだったんですね。すみません。また、ソートは関係がないと言うことでこのままやってみます。 <m(__)m>

その他の回答 (1)

papayuka
ベストアンサー率45% (1388/3066)

2005/06/05 21:58 回答No.1

別段おかしい所は無いと思いますが、、、 > 検索結果が、検索コードによって違うのです。検索値は数値ですか？実行後に #N/A も出ない？一覧表のA列を全て選択して、データ－区切り位置－完了とするとどうなる？（コピーブックでテストするか、保存しない事）

質問者

補足 2005/06/07 20:31

一覧表が昇順になっていないからだとわかりましたがこのエラーがでます。「#REF!」 '注意 Range("E24:H28").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,55,FALSE)" Selection.Value = Selection.Value この部分です。他にもでます。結合セルが問題? それとも、関数が実行されるセルの順序が問題なのでしょうか? また、検索結果が空白の場合、「０」が表示されるのを無くすことはできますか?

関連するQ&A

Excel VBAで表組みしたらデバック発生
Excel VBAの初心者です。Windows Vistaで Excel2007を使っています。表をマクロの実行で作成したいと思っています。何もないエクセルブックより「開発」→「マクロの記録」→「相対参照」 →「表の作成」→「記録終了」→「相対参照で記録の解除」 →「エクセルマクロ有効ブックで保存」ところがこのマクロ記録が入ったブックを再度立ち上げ、表をオールクリアにし、マクロボタンより表作成を実行させようとすると、次のエラーメッセージがでました。『実行時エラー'9' インデックスが有効範囲にありません。』デバックからModule1をみると以下の記述となっていました。 Sub 表組み() ' ' 表組み Macro ' ' ActiveCell.Range("A1:E5").Select Selection.Copy Windows("Book1").Activate ActiveSheet.Paste ActiveCell.Columns("A:A").EntireColumn.Select ActiveCell.Rows("1:1").EntireRow.RowHeight = 11.25 ActiveCell.Rows("1:5").EntireRow.Select Selection.RowHeight = 21.75 ActiveCell.Columns("A:A").EntireColumn.ColumnWidth = 14.88 ActiveCell.Offset(0, 4).Range("A1").Select Application.CutCopyMode = False With Selection .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With ActiveCell.Offset(1, -3).Range("A1:D4").Select Selection.NumberFormatLocal = "#,##0_ " ActiveCell.Select ActiveCell.FormulaR1C1 = "78000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "102000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "9800" ActiveCell.Offset(-2, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "65000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "204000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "500" ActiveCell.Offset(-2, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "86000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "151000" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "10200" ActiveCell.Offset(-2, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:RC[-1])" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-2]C:R[-1]C)" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" ActiveCell.Offset(0, -3).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" ActiveCell.Offset(0, 1).Range("A1").Select ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)" ActiveCell.Offset(-4, -2).Range("A1:D1").Select Selection.AutoFilter End Sub 上から9行目（？）のWindows("Book1").Activateに黄色い矢印が示され、また行全体が黄色く四角に覆われていました。おそらくこの記述に問題があると思いますが、どんな記述に変えたらいいのか分かりません。 Excel VBAにお詳しい方ご教示願います。なお、マクロで作成したい図を添付いたします。参考にしていただければ幸いです。
- ベストアンサー
- Windows Vista
VLookupで参照する範囲を変数で渡したい
マクロ内で開いた別ファイルにあるリストをVLookupで参照するマクロを作っています。固定のファイルで行う場合には以下の内容で動いています。 ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],'070801.xls'!R1C1:R2430C6,6,FALSE)" このファイル名[070801.xls]とセル範囲（行数がファイルによって違う）のを変数で渡す必要があります。＃細かい部分は割愛しています。 Workbooks.Open Filename:=Fname, ReadOnly:=True 'ファイル読み込み Range("A1").CurrentRegion.Select 'データ全体選択 Selection.SpecialCells(xlCellTypeLastCell).Select '最終行検出 endRcell = ActiveCell.Row ********ここの間に入る処理 ********多分「Range(Cells(1, 1), Cells(endRcell, 6))」など ThisWorkbook.Activate Range("H2").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-4],?????,6,FALSE)" 　　　　　　　　　　　　　　　　　　　　　　　　　　　　　↑ここに指定する変数２カ所について、どのように指定すればよいのか分かりません。色々試してみたのですが・・・・よろしくお願いいたします。
- 締切済み
- オフィス系ソフト
VBAで行をコピーして別のシートに貼付け
いつもお世話になっております。現場登録検索のシートの数値Ｆ2の値が一覧シートにマッチする行を検索しその行を切り取り、終了現場に入力されている最終行に貼付ける。と言うVBAを作りましたが、 "RangeクラスのSelectメソッドが失敗しました。" 下記が黄色になります。 Sheets("終了現場").Range("A1").Select 解決ができません。アドバイスをお願い致します。 Private Sub CommandButton7_Click() '終了ボタン '終了行を一覧から探す Worksheets("現場登録検索").Range("F2").Select ActiveCell.FormulaR1C1 = "=MATCH(RC[-1],一覧!C[-5],0)" n = ActiveCell.Value 行 = n Worksheets("一覧").Rows(行).Copy Sheets("終了現場").Range("A1").Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range("A1").Select ActiveSheet.Paste Application.CutCopyMode = False End Sub
- ベストアンサー
- その他（プログラミング・開発）
Excel VBAについて
以下のコードをFor Nextでまわすにはどうしたらよいでしょうか？ Sub sample() Range("A2").Select ActiveCell.FormulaR1C1 = "=テスト!R[1]C" Range("A3").Select ActiveCell.FormulaR1C1 = "=テスト!R[3]C" Range("A4").Select ActiveCell.FormulaR1C1 = "=テスト!R[5]C" Range("A5").Select ActiveCell.FormulaR1C1 = "=テスト!R[7]C" Range("A6").Select ActiveCell.FormulaR1C1 = "=テスト!R[9]C" Range("A7").Select ActiveCell.FormulaR1C1 = "=テスト!R[11]C" End Sub
- 締切済み
- オフィス系ソフト
VBAでエラーになってしまう
初心者です。 RangeクラスのSelectメゾットが失敗しました。となります。このコードは、マクロの記録を行い、そのコードをコマンドボタンのコードにコピーしたものです。初心者なので、マクロの記録からやりました。どうか、わかる方がいらっしゃいましたら、教えてください。 Private Sub CommandButton4_Click() Sheets("印刷ページ").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("C6").Select　←ここが黄色くなり、エラーになります。 ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("D6").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("E6").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("B8").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("C8").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("D8").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("E8").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("F8").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("B10").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("C10").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("D10").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" Range("E10").Select ActiveCell.FormulaR1C1 = "='鈴木　一郎'!R[2]C" End Sub
- ベストアンサー
- オフィス系ソフト
エクセルVBAが書ける方へ
お世話になります。下記VBAはB19からB28までのデータをA11:12の横列に貼り付けさせる内容です。 Sub 特価() ' ' 特価 Macro ' ' Range("A11:A12").Select ActiveCell.FormulaR1C1 = "=R[8]C[1]" Range("B11:B12").Select ActiveCell.FormulaR1C1 = "=R[9]C" Range("C11:C12").Select ActiveCell.FormulaR1C1 = "=R[10]C[-1]" Range("F11:F12").Select ActiveCell.FormulaR1C1 = "=R[11]C[-4]" Range("H11:I12").Select ActiveCell.FormulaR1C1 = "=R[12]C[-6]" Range("T11:U12").Select ActiveCell.FormulaR1C1 = "=R[13]C[-18]" Range("X11:Y12").Select ActiveCell.FormulaR1C1 = "=R[14]C[-22]" Range("AD11:AE12").Select ActiveCell.FormulaR1C1 = "=R[15]C[-28]" Range("AN11:AO12").Select ActiveCell.FormulaR1C1 = "=R[16]C[-38]" Range("AW11").Select ActiveCell.FormulaR1C1 = "=R[17]C[-47]" Range("B23").Select End Sub これに下記内容を追加したいのですが教えて下さい。・B19からB28のデータは入力後消す。・貼り付けたら次に入力する時は下の行(A13:14)に貼り付ける。・同様にデータを消してA15:16に貼り付ける。以下、下の行に貼り付けていくようにする。・貼り付けたらカーソルはB19を選択した状態にする。言葉足らずでしたら補足します。何卒宜しくお願い致します。
- 締切済み
- Excel（エクセル）
文字列を連続で置き換え・・・・？
マクロを記録したんですが手直しして繰り返しと空白のセル内が見つかった場合繰り返しを終わらせワードパッドにコピーして保存終了させたいんですがわかりません？誰か教えてください。 ' ' Macro2 Macro ' マクロ記録日 : 2012/4/21 ユーザー名 : cocoro ' ' Range("E:E").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""#"",RC[-4])" Range("F1").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""$"",RC[-4])" Range("G1").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""&"",RC[-4])" Range("E2").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""#"",RC[-4])" Range("F2").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""$"",RC[-4])" Range("G2").Select ActiveCell.FormulaR1C1 = "=SUBSTITUTE(RC[-1],""&"",RC[-4])" Columns("G:G").Select Selection.Copy Columns("H:H").Select Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _ xlNone, SkipBlanks:=False, Transpose:=False Columns("H:H").Select ActiveWindow.SmallScroll Down:=-6 Application.CutCopyMode = False Selection.Copy End Sub
- ベストアンサー
- Visual Basic
エクセルVBAの保存
毎月異なった新しいエクセルファイルに同じような加工を施すため、VBAを書きました。対象はActivesheetとしています。で、質問は、この新しいエクセルファイルの標準モジュールにいちいちこのVBAをコピーペーストせずに実行する方法です。きっと何かあるとは思うのですが・・・・。 VBAは次のような簡単なものです。 Sub 加工1() Dim e As Integer, s As String, n As String e = Range("A4").End(xlDown).Row s = Replace(Mid(Range("A2"), 8, 5), "年", "") & "-" n = Replace(Mid(Range("A2"), 19, 5), "年", "") & "-" Range("A1:C2").MergeCells = False Columns("B:B").Select Selection.Insert Shift:=xlToRight Columns("A:A").Select Selection.Insert Shift:=xlToRight Columns("C:C").Select Selection.NumberFormatLocal = "G/標準" Range("B3").Select Selection.AutoFill Destination:=Range("B3:C3"), Type:=xlFillDefault Range("B3").Select ActiveCell.FormulaR1C1 = "商品番号1" Range("C4").Select ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],10)" Range("C4").Select Selection.AutoFill Destination:=Range("C4:C" & e), Type:=xlFillDefault Range("A3").Select ActiveCell.FormulaR1C1 = "抽出年月日" Range("A4").Select ActiveCell.FormulaR1C1 = s & n & 1 Range("A4").Select Selection.AutoFill Destination:=Range("A4:A" & e), Type:=xlFillDefault Rows("3:3").Select Selection.Insert Shift:=xlDown Range("B1:E1").MergeCells = True Range("B2:E2").MergeCells = True ActiveSheet.Name = "提出用" End Sub
- ベストアンサー
- オフィス系ソフト
複数ファイルへのVBAの処理について
最近、ExcelのVBAを使うようになりました。しかし、以下のような処理を同じフォルダ内の複数のファイルに対して一気にやりたいのですが、わかる方いらっしゃいませんか？理想としては同じフォルダ内で50個くらいを選択して一気にやりたいのですが・・・。＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊ Sub FFT() Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlFixedWidth, _ FieldInfo:=Array(Array(0, 1), Array(18, 1)), TrailingMinusNumbers:=True Range("E8").Select Application.Run "ATPVBAEN.XLA!Fourier", ActiveSheet.Range("$B$1:$B$256"), _ ActiveSheet.Range("$C$1:$C$256"), False, False Columns("C:C").ColumnWidth = 38.38 Range("D1").Select Columns("D:D").ColumnWidth = 20.75 Range("D1").Select ActiveCell.FormulaR1C1 = "=IMABS(RC[-1])" Range("D1").Select Selection.Copy Range("D1:D256").Select ActiveSheet.Paste Range("E6").Select Application.CutCopyMode = False ActiveCell.FormulaR1C1 = "" Range("E7").Select End Sub
- 締切済み
- Visual Basic
vlookupで参照するシート名と範囲を変えたい
お世話になります。 vlookup関数について質問です。新規作成する表の列B2からF2（１行目は項目行）に別のブック「商品マスタ」からvlookupで必要なデータを検索して入力 B2からFのデータのある最終行までコピーするというマクロを作りました。 Range("B2").Select ActiveCell.FormulaR1C1 = _ "=VLOOKUP(RC1,[商品マスタ.xlsx]商品マスタ2017.10.5!R2C1:R430C6,COLUMN(RC),0)" Range("B2").Select Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault Range("B2:F2").Select Range("B2").Select Selection.AutoFill Destination:=Range("B2:F2"), Type:=xlFillDefault Range("B2:F2").Select Selection.AutoFill Destination:=Range("B2:F" & Cells(Rows.Count, "A").End(xlUp).Row) On Error Resume Next With Range("B:F").SpecialCells(xlCellTypeFormulas) .Value = .Value End With ★商品マスタの内容を更新時に新規シートを追加しているので《商品マスタ2017.10.5!R2C1:R430C6》この部分のシート名と参照範囲を毎回手動で書き直していますが、シート名と参照範囲を自動で切り替える方法がありましたら教えてください。シートは最新版のデータが入ったものを常に使用します。よろしくお願いします。
- ベストアンサー
- Excel（エクセル）