Excel VBA でVlookupがうまく走らない

前提・実現したいこと

Excel VBA でVlookupをするとエラーになって困っています。

発生している問題・エラーメッセージ

Compile Error. Invalid Character

該当のソースコードと試したこと

Excel
1'Range("H6").Formula = "=VLookup($AF6, 'Server\Folder[ABC20201021.xlsx]ABC_計算用'!$C:$V,H$2,FALSE)"'　
2の中の[]のABC20201021.xlsxですが、このファイルは毎日変わります。ファイル名のABCは変わらないのですが日付がYYYYMMDDフォーマットでその日の日付になります。
3
4'Range("H6").Formula = "=VLookup($AF6, 'Server\Folder[ABC & & Format(Now(), "yyyymmdd") & ".xlsx"]ABC_計算用'!$C:$V,H$2,FALSE)"'　に直すと$が認識しないみたいです。
5
6Range("H6").Formula = Application.VLookup($AF6, 'Server\Folder[ABC & & Format(Now(), "yyyymmdd") & ".xlsx"]ABC_計算用'!$C:$V,H$2,FALSE)　に変えてもやはり$が認識しないみたいです。
7
8何が間違っているのでしょうか？どなたかお分かりでしょうか？
9申し訳ないのですが、どうぞ宜しくお願い致します。

meg_

2020/10/21 22:52

「やはり$が認識しないみたいです。」とのことですが”絶対参照を止めると正常に動作する”という理解で良いでしょうか？

Usirow

2020/10/21 23:21

ダブルクォーテーションの扱いは大丈夫でしょうか？例えば2行目は"=Vlookup ~ の文を閉じずにFormat関数に移っていますし、3行目もServer\Folder\[ABCがダブルクォーテーションで括られていません。そのあたりをもう一度見直してみては如何でしょう？

行動規範の内容に同意します

回答1件

ベストアンサー

こういうことですか？

VBA
1Range("H6").Formula = "=VLookup($AF6, 'Server\Folder[ABC" & Format(Now(), "yyyymmdd") & ".xlsx]ABC_計算用'!$C:$V,H$2,FALSE)"

投稿2020/10/22 00:06

ttyp03

総合スコア16998

SnowBallEffect

2020/10/22 04:10

ありがとうございます！　できました！一つ、教えて頂きたいのですが、なぜ　[]の中のABCと&の間に"を入れないといけないのでしょうか？ Dim ABC as String ABC = "\server\folder\" ABC & Format(Now(), "yyyymmdd") & ".xlsx" こうだといつも思っていました。。。勉強不足ですいません。。

ttyp03

2020/10/22 05:57

要は数式を表す文字列を生成したいわけですよね。 VBAでの文字列は "～" で表し、文字列の連結は & を使います。また文字列の中に処理を書くことはできません。ですので、そのルールが守られていないということになります。質問文のものをそのルールで分割すると以下のようになります。 "=VLookup($AF6, 'Server\Folder[ABC & & Format(Now(), " yyyymmdd ") & " .xlsx "]ABC_計算用'!$C:$V,H$2,FALSE)" 1つ目の文字列の中に、VBAのFormat関数が入ってしまっています。 "～"の中にFormatはあくまでも単なる文字列であって、関数としては処理されません。 2つ目のyyyy～は文字列ではないのでここでコンパイルエラーになっているのでしょう。＆で連結もしていませんし。そのあとも同様です。

SnowBallEffect

2020/10/23 06:28

ttypo03さん、返信が遅くなり、すいませんでした。長文のご説明ありがとうございました！私の場合、「"～"の中にFormatはあくまでも単なる文字列であって、関数としては処理されません。」が分かっていなかったみたいです。クリアになったので、ありがとうございました！

行動規範の内容に同意します