VBAでのMATCH関数

VBAにて価格を算出するマクロを作成しています。

添付画像がイメージ図です。
表①にて型式・材質によりコードを取得
取得したコードに基づいて、MSHシートにある表②から価格を取得。

何種類化のパーツがあるため、①で取得したコードは配列 PRICE()に格納して、
MATCHを使って、②の該当行を特定し、価格を間接的に導く方式にしています。

PRICEへの格納は別のモジュールで行っており、Variant/Doubleとして数値格納は成功しています。

ただ実行してみると、最後の一つ（G_PRICE(2)の時）に実行時エラー１００４が出てしまいます。

VLOOKUPに変えてみたり、表の書式を「標準」「文字列」などに統一し直してみたりと試したのですが、エラーが止まることはありません。
最後の一つ以外はうまく行っており、また参照している①・②は共通なので、コードや表自体がおかしい可能性は低そうなのですが、なにかエラーを生じさせている可能性があれば、ご教示いただけないでしょうか。

【コード】

Sub 価格算出(ByRef P_PRICE() As Variant, ByRef G_PRICE() As Variant)

Dim i As Long '処理用の変数
Dim ptr As Double, gtr As Double '該当パーツNoのある該当行
Dim end_r As Long: end_r = MSH.Cells(Rows.Count, "B").End(xlUp).Row 'マスターシートの最終行
Dim t_range As Range: Set t_range = MSH.Range("B3:B" & end_r) 'Noを探す範囲

For i = 0 To 2

ptr = WorksheetFunction.Match(Trim(P_PRICE(i)), t_range, 0) + 2
P_PRICE(i) = MSH.Cells(ptr, 10)

gtr = WorksheetFunction.Match(Trim(G_PRICE(i)), t_range, 0) + 2
G_PRICE(i) = MSH.Cells(gtr, 10)


Next


End Sub

質問者からの補足コメント

• 回答ありがとうございます
  
  Trimを用いた理由としては、②の表のコードにはアルファベットを含んだものが有り、
  ②のコードは文字列としているためです。
  
  問題のモジュール以前の段階で、
  G_PRICE(), P_PRICE()ともに10桁の数字がVariant/Doubleで格納されています。
  
  問題のMATCHのところでは、ptrおよびgtrに該当業がdoubleで格納されますが、
  G_PRICE(2)では 0がDoubleで格納されたままになります。
  （Nextの前に、ptr=0, gtr=0deでリセットしています）

  No.2の回答に寄せられた補足コメントです。 補足日時：2022/10/18 15:47

No.3 ベストアンサー 回答者： Qchan1962 回答日時： 2022/10/18 17:03

回答べたでごめんなさい

＃２の補足は良く解りません
ptr = WorksheetFunction.Match(Trim(P_PRICE(i)), t_range, 0) + 2
は、P_PRICE(i)がDoubleであろうが、Doubleの初期値0であろうが、
はたまた、Longであろうが
Trim関数によりStringになっていますよ　と言う事です

文字列でMATCHの対象範囲B列で見つかればOK　
見つからなければ1004ですね

また、＞アルファベットを含んだものが有り
アルファベットをスペースに置き換えていると言う事でしょうか？
Trim関数：
文字列から先頭のスペースと、末尾のスペースを削除し結果を返す
P_PRICE(i)にスペースが含まれているのなら、そもそもの話になりますよ

あまりへんてこな書き方はしたくありませんがあくまで例として
ptr = WorksheetFunction.Match(CDbl(Trim(P_PRICE(i))), t_range, 0) + 2

Trim(P_PRICE(i))をDoubleに変換していますが見つかりますか？

この様なエラーはよくあるのでWorksheetFunction.Match関数でない
代替えを考えるべきかと思います
範囲が狭いのならFindとかFor　Nextでも対して負担にならないかと・・
（VLOOKUPで試しているようなので原因を知りたいだけだと理解していますが）

Dim ptr As Double, gtr As Double
これはMatch　, 0　の戻り値を代入する変数のようなのでLongで良い

一応Findの例
Dim r As Range
Set r = t_range.Find(What:=Trim(P_PRICE(i)), LookIn:=xlValues, LookAt:=xlWhole)
If Not r Is Nothing Then P_PRICE(i) = r.Offset(2, 8)

Offset：B列検索でJ列の見つかった+2下の値を取得

文字列として検索されますので表示形式に依存した検索となります
従って数値などの場合、表示形式の違いで検索されない場合があります
（汎用性を得るには、値の検証処理などを追加する必要があります）

この回答へのお礼

回答ありがとうございます。

いろいろと書いていただいたところ申し訳ないのですが、もっと原始的なミスでした。
表の表示形式を変えたつもりが、実際の入力値は変わっておらず、それにより検索が機能していなかったようです。
お騒がせして申し訳ありませんでした。

お礼日時：2022/10/19 17:58

No.2 回答者： Qchan1962 回答日時： 2022/10/18 00:26

Trim・・・　文字列を数値で・・いや逆かな？

Trimを無くすとどう？　各値を確認してみてください

Trim関数はstring（文字列）を返します
従ってTrim(P_PRICE(i))は文字列になります

このエラーはシート上でも同じだと思います。。
セル範囲には数字の1がある時
=MATCH("1",B1:B10,0)　　#N/A
=MATCH(1,B1:B10,0) 問題ない

No.1 回答者： Qchan1962 回答日時： 2022/10/17 23:35

こんばんは

シート上でもそうですけれどMatch関数って見つからないとエラーが返るのでは？
実行時エラー１００４が出たところでデバッグ、各値を確認してみてはどうでしょう