【マクロ】VLOOKUPにて参照元にデータが無い場合【該当なし】と表示したい

【マクロ】VLOOKUPにて参照元にデータが無い場合【該当なし】と表示したい

vlook関数にて、ブックのvlookupにデータを別ブックから
引っ張り反映させています

商品名のD、E、Ｆは参照元にデータが無い為
#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 ベストアンサー 回答者： fujillin 回答日時： 2024/06/08 17:42

こんにちは

以下、ご質問には直接関係のない内容も含まれますが、気が付いたので・・

1）関数式を設定する方法であれば、参照元のブックを開く必要はありません。
　参照元のブックの指定に、ファイルパスを加えてあげればそのまま（＝閉じたままでも）参照してくれます。
（手動で設定して参照元ブックを閉じた際に、式がそのように変わりますので、確認できるでしょう）

2）連続するセル範囲に関数式を設定するのにループする必要はありません。
　対象セル範囲に対して
　　　Range.Formula = "関数式"
　とすることで、まとめて設定することが可能です。
　（式は先頭のセルに対して、フィルコピーが可能な形式にしておきます）
　セル数が多い場合は、こちらの方が処理が速いです。

3）ご質問の内容ですが、関数式を利用しているのなら、IFERROR関数を利用すれば、元の式がエラーの場合に表示する内容を設定可能ですので、式をそのように変えれば済むでしょう。
https://support.microsoft.com/ja-jp/office/iferr …

この回答へのお礼

いつも、視野の広い知識をご指導ありがとうございます
以下３つの視点にて、ご指南ありがとうございました

１⇒知らなかったです
２⇒こちらも、知らなかったです。
３⇒マクロ関係なく考えればよかったのですね

お礼日時：2024/06/09 09:20

No.3 回答者： zongai 回答日時： 2024/06/09 09:49

""【該当なし】"" の部分について。

ざっくり言えば、
『"…"の中で「"」を使いたければ「""」と書け』
という記述上のルールがあるから。


問題
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 回答者： zongai 回答日時： 2024/06/08 17:17

こう書き換えてみてはどう？

TargetSheet.Cells(i, "B").Formula = "=IFERROR(VLOOKUP(A" & i & ",[list.xlsx]商品!$A:$B, 2, FALSE), ""【該当なし】"")"

この回答へのお礼

ご教授頂きまして、お返事ありがとうございます
動きました。

１つ質問なのですが、当該コードの　 ""【該当なし】""　は
文字列を　""　２つで囲っています

これはなぜですか？マクロ関係ない以下のような式ですと
”　は１つだけでかこっています

お手すきな時に、宜しくお願いします

IFERRORの式：=IFERROR(VLOOKUP(A4,A7:D17,2,FALSE),"該当商品無し")

お礼日時：2024/06/09 08:05