質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.50%

【Excel VBA】VLOOKUPがエラーになる原因が分からない

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 2,036

いつもこちらの掲示板には大変お世話になっております。
現在VBAを勉強中の初心者です。皆様のおかげで毎日大変勉強になっております。

今回もどなたかのお知恵をお借りしたく思い、ご相談させていただきました。
文中に拙い部分も多々あるかと存じますが、何卒ご容赦いただけますと幸いです。

前提・実現したいこと

業務上で発生するデータ転記の作業の一部を、マクロで行えたらと思っております。
具体的には、管理番号を手動でシート①に入力してもらい、管理番号に連携した情報をシート②に転記したいです。

シート①↓
イメージ説明

シート②↓
イメージ説明

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

管理番号をシート②に転記する部分まではうまくいくのですが、
最後のVLOOKUPの部分でエラーメッセージが出てしまいます。

ネットで先人様方がネット公開されている例を拝見し、何回かコードを書き換えたりもしましたが
毎度同じエラーを発生させてしまいます……

もしかしたら、Aセルの管理番号はDとEを組み合わせたものであり
範囲を取得する部分で値ではなく計算式を見ているからかもしれません。

問題の箇所に気づかれた方がおられましたら、お手数をお掛けしますが教えていただけませんでしょうか?
(検索および考えの甘い部分がありましたら申し訳ありません)

worksheetfunctionクラスのvlookupプロパティを取得できません

該当のソースコード

Sub ①シートに転記するマクロ()

    Dim マスデ As Workbook
    Set マスデ = ThisWorkbook

    Dim 最終行 As Long
    最終行 = マスデ.Worksheets("情報_01").Cells(Rows.Count, 1).End(xlUp).Row '管理番号の最終行を取得

    Dim 管理番号 As String
    管理番号 = Application.InputBox("管理番号を入力してください") '「管理番号」の入力

    Dim Target As Range
    Set Target = Range(Cells(2, 1), Cells(最終行, 1)).Find(What:=管理番号, LookIn:=xlValues, LookAt:=xlWhole)

        If Target Is Nothing Then '「管理番号」がなかった場合は終了
            MsgBox "管理番号が存在しません"
            Exit Sub
        End If

    Workbooks.Open Filename:="C:\ひな形.xlsx"

    Dim シート As Worksheet 'ひな形のシートを開く
    Set シート = Worksheets(1)
    シート.Activate

    With ActiveSheet

        .Range("A2").Value = 管理番号
        .Range("B2").Value = Application.WorksheetFunction.VLookup(管理番号, マスデ.Worksheets("情報_01").Range("A1:C100"), 2, False) '計算結果を検索する

    End With

End Sub
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

+2

LookIn:=xlValues が指定されてますので、数式をみているということはないはずです。
サンプルを再生して提示のコードを実行してみましたがエラーなく正しい関連情報が出力されました。
管理番号の式を =D2*100+E2 というように数値型になるように設定すると質問のエラーになりますね。
=D2&E2と文字列型になる式で確認してました。

ただ、Findで該当セル(Target)は見つかってますので、それを使えば、わざわざVLookupでもう一度検索する必要はないですね。

    With ActiveSheet

        .Range("A2").Value = 管理番号
        .Range("B2").Value = Application.WorksheetFunction.VLookup(管理番号, マスデ.Worksheets("情報_01").Range("A1:C100"), 2, False) '計算結果を検索する

    End With


の部分は、下記の1行でいいですね。

ActiveSheet.Range("A2:C2").Value = Target.Resize(, 3).Value

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/01/17 06:33 編集

    ご回答いただきありがとうございます!
    Resizeプロパティを使う方法、目からウロコでした!確かにいちいちVLOOKUPを使うくらいなら、
    範囲を選択して一度に移動させてしまうやり方の方がシンプルかつ楽ですね…

    こちらに載せているサンプルの表を簡易的に作った私が悪いのですが、
    実際のファイルは隣り合ったセルを転記するのではなく、
    ・シート①「A1セル」の情報をシート②「A1セル」へ、
    ・シート①「B1セル」の情報をシート②「D5セル」へ、
    ・シート①「C1セル」の情報をシート②「G10セル」へ
    といったように転々とした箇所に転記する必要があります。
    そうなった場合、VLOOKUPの方が都合がいいのかなと思っていたのですが、
    差し支えなければhatena19様の更なるご意見を伺ってもよろしいでしょうか?

    キャンセル

  • 2021/01/17 09:44 編集

    シート①のデータが隣接しているのでOffsetを使えばいいでしょう。

    With ActiveSheet
    .Range("A1").Value = Target.Value
    .Range("D5").Value = Target.Offset(1).Value
    .Range("G10").Value = Target.Offset(2).Value
    End With

    キャンセル

  • 2021/01/18 09:19

    お手数にも関わらず、再度コメントいただきありがとうございました!
    無事に転記ができました。hatena19様のアイデアのおかげで仕事が捗りそうです!
    VLOOKUPにこだわりまくってドツボにハマっていましたが、
    他に簡単なやり方がないか考えを巡らせる重要性を痛感しました…
    この度は初歩的な内容にも関わらず、ご助言いただき本当にありがとうございました!

    キャンセル

+2

原因について

おそらく、シート「情報_01」のA列(「管理番号(D+E)」の列)に入っている管理番号のデータが数値として入っているため、VBA 条でString 型として定義されている変数 「管理情報」とのマッチングでマッチしないのだと思われます。
ISNUMBER()関数などで当該セルを確認してみてください、おそらくTRUE となると思います。

解決方法

解決方法としては、以下のように、当該列のデータを文字列にすることが考えられます。

  1. 当該列の選択し、セル書式で表示形式を「文字列」にする。
  2. 当該列のデータをテキストエディタなどにコピーしたのち、再度貼り付ける。

VBA プログラム上で、「管理番号」を数値(Integer)にする方法も考えられますが、あまりお勧めしません。
理由は、「管理番号」が数値というよりもIDとして扱われていると思われるため 「010100」といった、ゼロから始まる番号が出現する可能性も考えられ、その場合はうまく処理できなくなってしまうためです。

この種のエラーを調べる方法について

Excel の関数を VBA で使っていて思った通りに動かない場合は、
いったん VBA から離れて、元のExcel の関数で動作を確認してみるのが
早道だと思います。

今回の場合なら、同一ブックに適当なシートを追加するなりして、
適当なセルに以下のような式を入れてみれば、
すくなくとも、VBAの問題か、Excel 関数の問題かの切り分けはすぐにできます。

=VLOOKUP(A1, 情報_01!A1:D3, 2, FALSE)


※A1 は任意のセルを指定

ここまで調べて、Excel 関数の問題だと判明した状態なら、Excel の問題として質問できるので、回答できる人も多くなり、より良い回答がより速く得られるでしょう。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/01/16 23:23

    ご回答いただきありがとうございます!
    エラーの内容だけでなく、エラーの解決手段のアドバイスも本当に有難いです。
    セルの書式で「文字列」にするというアイデア、いただきました!
    数値型への変換ってプログラムでできないのかな?と思っていたのですが、
    そんなところに思わぬ弊害があるのですね…勉強になりました。

    そして、ISNUMBER関数やVLOOKUP関数の確認など
    確かにまずはそれで確認すればよかった!と反省しました…
    確かに局部的に考えると、Excel関数レベルの問題でしたね…もっと分解して考えるべきでした。

    順を追って丁寧に教えていただきありがとうございました!
    アドバイスいただいた箇所を修正してはみたのですが、
    同じエラーを出してしまいましたので、他の箇所でもミスってしまっているようです…
    しかし、大変勉強になった上に、解決に一歩近づいたと思われます!
    コメントでご教授いただき本当にありがとうございました!

    キャンセル

0

InputBoxを受け取る変数がstring型で指定されているため、数値で入力されているワークシート内の管理番号を検索出来ていないのだと思われます。管理番号という変数を数字型で宣言してみてください。

変数に「10010」という数字を入力するとき、文字列型(String)の場合には"10010"が格納されます。
一方、数字型(IntegerやLongなど)の場合には10010が格納されます。

この型の違いは、検索で不具合を起こすことが多いです。わたしもときどきやります。

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2021/01/17 07:05

    ご回答いただきありがとうございます!
    string型の場合は""が付くのですね…!確かに文字列を入力するときは""で囲みますね…盲点でした。
    これからは型に気を付けて組んでみます。とても勉強になりました!
    初歩的な内容にも関わらずご教授いただき本当にありがとうございました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る