![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?8acaa2e)
【マクロ】VLOOKUPにて参照元にデータが無い場合【該当なし】と表示したい
vlook関数にて、ブックのvlookupにデータを別ブックから
引っ張り反映させています
商品名のD、E、Fは参照元にデータが無い為
#N/Aとなります。ここのエラーではなく、【該当なし】
と表示をさせたいです
参照元にデータが無い場合は、該当なしと表示させたいという
いうことです
ご存じの方、いらっしゃいましたら、教えて下さい
【データ反映先のブック】
"C:\Users\2020\OneDrive\マクロ\vlookup.xlsm"
シート名:反映先
A列 B列 C列
1行目 商品名 単価
2行目 A 10
3行目 B 20
4行目 C 30
5行目 D #N/A
6行目 E #N/A
7行目 F #N/A
【データ参照元のブック】
"C:\Users\2020\OneDrive\マクロ\list.xlsx"
シート名:商品
A列 B列 C列
1行目 商品名 単価
2行目 A 10
3行目 B 20
4行目 C 30
【コード:動きます】
Sub 開いたままvlookup別ブック()
Dim TargetWorkbook As Workbook
Dim TargetSheet As Worksheet
Dim SourceWorkbook As Workbook
Dim LastRow As Long
Dim i As Long
Dim myRng As Range
Dim filePath As String
filePath = "C:\Users\2020\OneDrive\マクロ\vlookup.xlsm"
Set TargetWorkbook = Workbooks.Open(filePath)
Set TargetSheet = TargetWorkbook.Sheets("反映先") ' ターゲットシートの名前を適宜変更
Set SourceWorkbook = Workbooks.Open("C:\Users\2020\OneDrive\マクロ\list.xlsx")
' ターゲットシートのA列の最終行を取得
LastRow = TargetSheet.Cells(TargetSheet.Rows.Count, "A").End(xlUp).Row
' A2セルからA列をループ
For i = 2 To LastRow
' VLOOKUP関数の数式を設定
TargetSheet.Cells(i, "B").Formula = "=VLOOKUP(A" & i & ",[list.xlsx]商品!$A:$B, 2, FALSE)"
Next i
End Sub
No.2ベストアンサー
- 回答日時:
こんにちは
以下、ご質問には直接関係のない内容も含まれますが、気が付いたので・・
1)関数式を設定する方法であれば、参照元のブックを開く必要はありません。
参照元のブックの指定に、ファイルパスを加えてあげればそのまま(=閉じたままでも)参照してくれます。
(手動で設定して参照元ブックを閉じた際に、式がそのように変わりますので、確認できるでしょう)
2)連続するセル範囲に関数式を設定するのにループする必要はありません。
対象セル範囲に対して
Range.Formula = "関数式"
とすることで、まとめて設定することが可能です。
(式は先頭のセルに対して、フィルコピーが可能な形式にしておきます)
セル数が多い場合は、こちらの方が処理が速いです。
3)ご質問の内容ですが、関数式を利用しているのなら、IFERROR関数を利用すれば、元の式がエラーの場合に表示する内容を設定可能ですので、式をそのように変えれば済むでしょう。
https://support.microsoft.com/ja-jp/office/iferr …
いつも、視野の広い知識をご指導ありがとうございます
以下3つの視点にて、ご指南ありがとうございました
1⇒知らなかったです
2⇒こちらも、知らなかったです。
3⇒マクロ関係なく考えればよかったのですね
No.3
- 回答日時:
""【該当なし】"" の部分について。
ざっくり言えば、
『"…"の中で「"」を使いたければ「""」と書け』
という記述上のルールがあるから。
問題
A1セルに「="test"」という数式を書き込むコードを考えてみてください。
Cells(1, 1).Formula = "="test""
間違いです。
Cells(1, 1).Formula = "="
この後に無意味な文字列が続いているコードになります(エラー)
答え2例
Sub test()
Cells(1, 1).Formula = "=""test"""
Cells(2, 1).Formula = "=" & Chr(34) & "test" & Chr(34)
End Sub
A1セルに書き込まれる式は「"」を「""」で表記したもの。
別解
A2セルに書き込まれる式は「"」をChr(34)として表記したもの。
同じ結果になります。
> IFERRORの式:=IFERROR(VLOOKUP(A4,A7:D17,2,FALSE),"該当商品無し")
仮にこの数式をセルに書き込網とした場合、
" =IFERROR(VLOOKUP(A4,A7:D17,2,FALSE),"該当商品無し") "
というように、"…"の中で「"」を扱うことになります。
こういうときには"…"の中での「"」は「""」に置き換えてあげるのです。
ざっくりじゃない説明はこちらとか、他検索してみてください。
↓
Office TANAKA - Excel VBA Tips[ダブルコーテーションの表示]
http://officetanaka.net/excel/vba/tips/tips90.htm
No.1
- 回答日時:
こう書き換えてみてはどう?
TargetSheet.Cells(i, "B").Formula = "=IFERROR(VLOOKUP(A" & i & ",[list.xlsx]商品!$A:$B, 2, FALSE), ""【該当なし】"")"
ご教授頂きまして、お返事ありがとうございます
動きました。
1つ質問なのですが、当該コードの ""【該当なし】"" は
文字列を "" 2つで囲っています
これはなぜですか?マクロ関係ない以下のような式ですと
” は1つだけでかこっています
お手すきな時に、宜しくお願いします
IFERRORの式:=IFERROR(VLOOKUP(A4,A7:D17,2,FALSE),"該当商品無し")
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) (マクロ)vlookupの元データを同じブックのシートではなく、別のブックに設定したい 1 2024/06/02 10:03
- Visual Basic(VBA) 【ご教示ください】VBAの記述方法がわかりません。 2 2022/08/12 21:28
- Visual Basic(VBA) 【前回の続き続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/24 20:49
- Visual Basic(VBA) 【前回の続きです、ご教示ください】VBAの記述方法がわかりません。 2 2022/08/16 16:44
- Excel(エクセル) 【マクロ】その時、その時で変わる範囲を、フォルダの違う別ブックへ転記する為には ※参考画像あり 3 2024/05/11 10:33
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) VBA 複数の各シートに行を追加したいです 2 2024/02/28 22:09
- その他(Microsoft Office) マクロVBAについて 1 2022/09/06 18:12
- Visual Basic(VBA) クリップボードに貼付している文字列が、マクロで別ブックへ転記すると、消えてしまう 1 2023/10/15 13:36
- Visual Basic(VBA) 【マクロ】1つのマクロの中に、ブック指定とシート指定が混在しても良いのですか? 2 2024/05/16 07:13
このQ&Aを見た人はこんなQ&Aも見ています
-
「どうして捨てられないの?」前妻の物を捨てられない男性の心理って?
前妻の物を捨てられない理由に加え、捨てるための手段はあるのかを専門家に聞いてみた!
-
Excelにてある膨大なデータを管理しています。 そこで、特定の市町村にのみ色を付けたいです。 Ex
Excel(エクセル)
-
エクセルの計算
Excel(エクセル)
-
エクセルで上位バイトのセルと下位バイトのセルを1つのセルにして16進数を作る方法
Excel(エクセル)
-
-
4
EXCELの質問です 119から足したり引いたりしたいです E3には「=119+C3+D3」が入って
Excel(エクセル)
-
5
【マクロ】顧客番号にて一致させ、情報を表へ上書きする為には
Excel(エクセル)
-
6
Excelにて数式を使用して 工数計算をしたいのですが、 計算方法が分からないので教えて下さい。 1
Excel(エクセル)
-
7
【関数】スペースがいくつ入った後の文字列を取り出したい
Excel(エクセル)
-
8
エクセル②
Excel(エクセル)
-
9
指定文字の間に
Excel(エクセル)
-
10
関数の説明
Excel(エクセル)
-
11
エクセルでセルに標準で入力されている和暦を西暦に変換する方法はありますか?
Excel(エクセル)
-
12
エクセルの関数の勉強の仕方
Excel(エクセル)
-
13
下記マクロでMsgBox 空白です。"の部分の メッセージボックスは現れるものの 空白です。の文字"
Excel(エクセル)
-
14
エクセル共有したが、アクセスできなくかった
Excel(エクセル)
-
15
Excelで、10000,20000,30000と表示されているのですが、時々10000,20000
Excel(エクセル)
-
16
スマートな関数を教えて下さい。
Excel(エクセル)
-
17
リンク先が空白若しくはゼロの場合の処理
Excel(エクセル)
-
18
エクセルを使っていて2024/5/15と入力したのに令和元年とかででるのでしょうか?何か設定分かる方
Excel(エクセル)
-
19
excel で二つのどちらかを選ぶ
Excel(エクセル)
-
20
エクセル 文字を増やしたい。
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excel分数の表示について
-
Excelについての質問です。 B2...
-
EXCELの散布図で日付が1900年に...
-
文字2桁、3桁交じりの文字列...
-
【EXCEL】画像の黄色部分の抽出...
-
マクロエクセルのブロック解除
-
絶対参照
-
Excelで表を作ったところに文字...
-
Excelピボットテーブルの1行目
-
エクセルのクイックアクセスツ...
-
DATE関数で現在の年齢を出した...
-
(マクロ)vlookupの元データを同...
-
PDFの請求明細をエクセルにしたい
-
エクセルのクイックアクセスツ...
-
REGEXREPLACE関数について、
-
職場の人から聞かれており、こ...
-
エクセルの空欄をつめて、次の...
-
ユーザー定義関数をアドイン登...
-
EXCELの質問です 119から足した...
-
エクセルの問題です。絶対値の...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【マクロ】VLOOKUPにて参照元に...
-
エクセルに入力後、別のシート...
-
EXCEL VBA データを抽出して別...
-
エクセルデータから必要な情報...
-
エクセルのシートの縦横入れ替え
-
エクセルの最終ページだけを自...
-
煙草の名称を覚える方法で…
-
マクロや関数で来店管理表を作...
-
エクセルマクロ:日付値で、別...
-
Excel VBA - 列をシート別に分ける
-
ブック間参照しながら3つ以上の...
-
ExcelのVBAでvlookupの使い方を...
-
Caps Lockキーの解除
-
Excel2010の並べ替えで行の高さ...
-
リース初心者です 利子率の計...
-
Excelでカーソルが逆に動く
-
エクセルで複数の勤務時間ごと...
-
エクセル関数で {=TABLE(,セル...
-
プルダウンで選択すると隣のセ...
-
エクセルで離れたセルを離れた...
おすすめ情報