[[20200918200147]]　『VBA　VLOOKUPについて』（ピノ）　ページの最後に飛ぶ

『VBA　VLOOKUPについて』（ピノ）

いつもお世話になっております。

前回質問させていただいた内容と少しかぶりますが、
VBAにてVLOOKUPを使用する場合の記載方法について
どうしてもわからないところがあり、教えてください。

＜ご教示いただきたいこと＞

以下の部分です。

        Range("g7") = "=VLOOKUP(a7,[" & BNファイル.Name & "]在庫!$A$1:$r$10000,11,0)"

VLOOKUPの検索範囲を変数にしているのですが、
「アプリケーション定義または帯ジェクト定義のエラー」となってしまいます。改めて、書き方を教えていただきたいです。

※ちなみに、VLOOKUPとは関係ないですが、以前ご指摘いただいた箇所で
日付変数の取得方法ですが、決まった曜日に自動実行するマクロの為、
日付の変数は、今日から見た〇日前という指定をしています。

Sub データ加工()

    Dim LR As Long
    Dim i As Long, J As Long
    Dim pv As PivotTable
    Dim sh As Worksheet
    Dim 一時ファイル As Workbook, 完成ファイル1 As Workbook
    Dim fpath As Variant

    Const MyPath As String = "C:\Users\mi\Desktop\ライブラリー\週間発注表作成\最終\完成データ1"
    Dim 開始日 As String, 終了日 As String
    Dim 前週開始日 As String, 前週終了日 As String
    Dim BNファイル As Workbook '前週のファイル

    'テキストデータをエクセル形式にして開く
    Workbooks.OpenText Filename:= _
        "C:\Users\mi\Desktop\ライブラリー\週間発注表作成\元データ\週間発注データ.csv" _
        , Origin:=932, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False _
        , Comma:=True, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), Array(7, 1), Array(8, 1), _
        Array(9, 1), Array(10, 1), Array(11, 1), Array(12, 1), Array(13, 1), Array(14, 1), Array(15, 1), Array(16, 1), Array(17, 1), Array(18, 1), Array(19, 1), Array(20, 1), Array(21, 1), Array(22, 1), Array(23, 1), Array(24, 1)), TrailingMinusNumbers:=True

        '週間発注日付をファイル名に設定
        開始日 = Format(Date + 5, "mmdd")
        終了日 = Format(Date + 11, "mmdd")

        ActiveWorkbook.SaveAs Filename:=MyPath & "\" & "週間発注" & 開始日 & "-" & 終了日 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
        Set 完成ファイル1 = ActiveWorkbook

  '---------1週間前の週間発注表の水～土のデータを表示---------

        '1週間前のファイルを開く

        前週開始日 = Format(Date - 4, "mmdd")
        前週終了日 = Format(Date + 4, "mmdd")

        Workbooks.Open Filename:=MyPath & "\" & "週間発注" & 前週開始日 & "-" & 前週終了日 & ".xlsx"

        Set BNファイル = ActiveWorkbook

        完成ファイル1.Activate
        Worksheets("在庫").Select

        '列挿入
        Columns("g:j").Insert

        '前週データ残日数分をVLOOKで表示
        Dim 前週残(1 To 4) As Variant

        前週残(1) = Format(Date - 6, "yyyymmdd")
        前週残(2) = Format(Date - 5, "yyyymmdd")
        前週残(3) = Format(Date - 4, "yyyymmdd")
        前週残(4) = Format(Date - 3, "yyyymmdd")

    '▼前週水曜日データを貼付け
        Range("g6") = 前週残(1)

'★★★VLOOK要確認★★★

        Range("g7") = "=VLOOKUP(a7,[" & BNファイル.Name & "]在庫!$A$1:$r$10000,11,0)"

        'フィルダウン
        Range("g7:g" & LR).FillDown

'ほかの曜日も同じ処理

    '-----ここまで----1週間前の週間発注表の水～土のデータを表示---------

End Sub

＜使用 Excel：Excel2016、使用 OS：Windows10 ＞

新しい質問するまえに↓片づけてもらえませんか？
[[20200911193145]]　『VBA　❶VLOOKUP,❷最終行カウント分の行挿入をしたい』（ピノ）

無視されてるようで不愉快です。

（もこな２） 2020/09/18(金) 20:20

もこな2様
大変申し訳ありません。
コメントいただいていたのに見落としておりました。
本件については、VLOOKUP関数がどうしてもわからなかったため、
別ブックを作成し、そこにVLOOKの数式を予め組んでおき、
比較する前週と当週のデータを貼り付ける、という流れで完成させました。

今回のものも同じ工程を出来なくはないですが、
VLOOKUPをVBAで使用する頻度が高そうなので、
毎回フォーム作成よりVLOOKUPをきちんと習得したいと思い、
改めて投稿させてもらいました。
（ピノ） 2020/09/18(金) 21:16

＞コメントいただいていたのに見落としておりました。
そうですか。見落としていたということなら、後ほどレス頂くことは可能ですね。
向こうは向こうで進めましょう。

以下、こちらのトピックについてです。
■１
＞決まった曜日に自動実行するマクロの為、
＞日付の変数は、今日から見た〇日前という指定をしています。
再三指摘してますが、日付を扱うなら日付型にすべきでしょう。
特定の曜日の日付を得たいなら例えばこんな感じでよいとおもいます。

    Sub 実験()
        Dim 日付 As Date

        日付 = InputBox("yyyy/mm/dd形式で入力")
        MsgBox 日付 & "が属する週の水曜日は" & vbLf & 日付 - (Weekday(日付) - 4) & "です"
    End Sub

■２

 Range("g7") = "=VLOOKUP(a7,[" & BNファイル.Name & "]在庫!$A$1:$r$10000,11,0)"

まず、以前からですが、セルの名前はG7、A7セルです。
Excel君の忖度機能のおかげで問題はでませんが、ものすごく厳密にいえば大文字と小文字は別物です。

また、同じく忖度機能で、Valueプロパティを"省略"したとみなされています。

 Range("g7") 
 ↓
 Range("g7").Value

さらに、Valueへの代入であっても"="で始まる文字列である場合、これまた忖度機能で数式として認識してくれます。
ただ、数式を設定するなら本来はFormulaプロパティを使うべきでしょう。
（説明はしませんでしたが、実は[[20200911193145]]の■７で示しています。向こうにレスを付けるときにでも確認してみてください）

踏まえると、↓のように記述したほうがよいでしょう。

 Range("G7").Formula = "=VLOOKUP(A7,[" & BNファイル.Name & "]在庫!$A$1:$R$10000,11,0)"

そして、こちらで↑を試したところ問題はでませんでしたから、「アプリケーション定義または"オブ"ジェクト定義のエラー」となったところで、本当にBNファイルに"在庫"という名前のシートがあるのか確認してみてください。

（もこな２） 2020/09/18(金) 21:49

もこな２様

ご返信ありがとうございます。

＞コメントいただいていたのに見落としておりました。
そうですか。見落としていたということなら、後ほどレス頂くことは可能ですね。

 向こうは向こうで進めましょう。

…こちらについては、お伝えしたやり方で、解決しましたので、
コメントいただかなくても大丈夫ですが、後ほどレスさせていただきます。

■１について
有難うございます。
毎回カレンダーで調べていたので、頂いたコードを活用させていただきます。

また、型でDateを使用しなかった理由は、

        前週残(1) = Format(Date - 6, "yyyymmdd")
で記載してDate型にすると、型が一致しないエラーが出るためVariantにしていました。
上記の書き方をする場合は、どの型が適切なのでしょうか。

■２について

＞まず、以前からですが、セルの名前はG7、A7セルです。
Excel君の忖度機能のおかげで問題はでませんが、ものすごく厳密にいえば大文字と小文字は別物です。
…失礼しました。そうなんですね…通信教育のテキストでは、小文字でもOKと記載されていたので、スピードが上がると思って小文字を使用していました。。。

＞同じく忖度機能で、Valueプロパティを"省略"したとみなされています。

…Value、Formulaプロパティも、省略しても問題なさそうだったので省いて記載していましたが、本来であれば省略しないほうがいいのですね。それによりエラーになることもあるのでしょうか？

頂いたコードでかつBNファイルに在庫シートが存在するのですが、できずでしたが、
イミディエイトウィンドウでデバック検証をして、ファイルとシート名の前後に''をつけたらうまくできるようになりました。
恥ずかしながら、イミディエイトウィンドウ、の使い方、
今回のエラーで初めて使い方がわかった気がします。

 Range("G7") = "=VLOOKUP(A7,'[" & BNファイル.Name & "]在庫'!$A$1:$R$10000,11,0)"

いつもありがとうございます。
（ピノ） 2020/09/19(土) 17:56

＞それによりエラーになることもあるのでしょうか？
以下を再読ください。
＞＞Valueへの代入であっても"="で始まる文字列である場合、これまた忖度機能で数式として認識してくれます。

ちなみに取得のほうではダメなのはわかりますよね。

（もこな２） 2020/09/19(土) 19:11

もこな2様

有難う御座います。
忖度で数式として認識してくれるから大丈夫ということですね。

取得の方とはvalueのことでしょうか。
valueとformulaの違いがあまり分かってなかったので、役割について以下で学びました。
有難う御座います。
http://officetanaka.net/excel/vba/cell/cell03.htm

（ピノ） 2020/09/19(土) 20:55

コメント返信：

[ 一覧(最新更新順) ]

[[20200918200147]] 『VBA VLOOKUPについて』（ピノ） ページの最後に飛ぶ

[[20200918200147]]　『VBA　VLOOKUPについて』（ピノ）　ページの最後に飛ぶ