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

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

新規登録して質問してみよう
ただいま回答率
85.51%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

4回答

3746閲覧

ExcelVBAでVLOOKUPの表記方法

rura

総合スコア70

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2016/12/13 06:19

編集2016/12/14 05:16

###前提・実現したいこと
福祉施設のタイムカードを作ってます。
VLOOKUPで氏名、受給者番号、事業者番号、所属先をフォームの受給者番号から
利用者シートの(所属先名)(氏名)(所属先事業者番号)から
値を取ってきて、結果を個別TC集計表に張りたいのです。
イメージ説明
貼付け先
検索元
###発生している問題・エラーメッセージ

フォームの受給者証番号から検索しても、VLOOKUPの値が出てきません。 応答無しという表記とともに動作がとても遅くなります。 ###該当のソースコード ```ここに言語を入力 '受給者証番号を元に氏名、所属先、所属先事業者章番号をVBAで入力 Dim TCBsNum As String '氏名 Dim ACAft As String '所属先 Dim ACBusinessNo As String '所属先事業者章番号 Dim ACBnsName As String '事業所及びその他の事業所 On Error Resume Next '氏名 TCBsNum = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 4) '所属先 ACAft = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 3) '所属先事業者章番号 ACBusinessNo = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 12) '事業所及びその他の事業所 D5 ACBnsName = WorksheetFunction.VLookup(Sheet6.Range("G5"), Sheet2.Range("A5:L1000"), 2) On Error GoTo 0

###補足情報(言語/FW/ツール等のバージョンなど)
ExcelVBA windows10 Excel2016

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

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答4

0

1)氏名、所属先、所属先事業者章番号が、いずれも定義不明なACYearをキーにvlookupされています。
フォームの受給者番号をキーにするのであれば、受給者番号を読み出すコードが必要なはずです。
2)vlookupの4つ目の引数が指定されていません。FALSEを指定してください。
4つ目の引数にFALSEを指定すると完全一致したデータが検索されます。
指定しなかったり、TRUEを指定すると完全一致したデータがなければ近所にあるデータ(関係がないかもしれないデータ)が検索されてしまいます。
「VLOOKUPの値が出てきません」の原因になっているのは、これではないかと推測します。

Sheet6.Range("G5")が、受給者番号が記入されるセルだとすると、質問に書かれたコードは以下のように修正すべきと思われます。

'受給者証番号を元に氏名、所属先、所属先事業者章番号をVBAで入力 Dim TCBsNum As String '氏名 Dim ACAft As String '所属先 Dim ACBusinessNo As String '所属先事業者番号 Dim ACBnsID As String '受給者番号 '追加しました Dim UserTable as Range '利用者シート '追加しました On Error Resume Next '利用者シートを変数に設定 set UserTable = Sheet1.Range("A5:L1000") '受給者番号をシートから読み取る ACBnsID = Sheet6.Range("G5") '氏名 TCBsNum = Vlookup(ACBnsID,UserTable,4,false) '所属先 ACAft = Vlookup(ACBnsID,UserTable,3,false) '所属先事業者番号 ACBusinessNo = Vlookup(ACBnsID,UserTable,12,false) '事業所及びその他の事業所 ACBnsName = Vlookup(ACBnsID,UserTable,2,false) On Error GoTo 0

投稿2016/12/13 08:04

coco_bauer

総合スコア6915

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rura

2016/12/13 10:41

ご回答下さりまして、誠にありがとうございます。 試しにコピペで上記内容を試したのですが、 やはり空白で入力してくれません。
coco_bauer

2016/12/14 01:17

「空白で入力してくれません」というのは、何が空白だというのですか? 入力してくれませんとは、何に入力されてないのでしょうか? 質問に書かれたコードや、私が回答したコードには、変数にvlookupした結果を代入するところまでしかありません。 セルに値が入るようなプログラムになっていないことを理解して頂いていますか?
guest

0

ベストアンサー

質問内容をみると、そもそもVLOOKUP関数をよく理解していないと思われます。
さらに、最初のご質問のあったソースコードですが、いくつかの問題があります。

'受給者証番号を元に氏名、所属先、所属先事業者章番号をVBAで入力

Dim TCBsNum As String '氏名
Dim ACAft As String '所属先
Dim ACBusinessNo As String '所属先事業者章番号
Dim ACBnsName As String '事業所及びその他の事業所

On Error Resume Next

'氏名
TCBsNum = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 4)
'所属先
ACAft = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 3)
'所属先事業者章番号
ACBusinessNo = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 12)
'事業所及びその他の事業所 D5
ACBnsName = WorksheetFunction.VLookup(Sheet6.Range("G5"), Sheet2.Range("A5:L1000"), 2)
On Error GoTo 0

1.VLOOKUPの引数の検索値の引数が ACYear になっている
質問では、「受給者証番号」が引数になるので、ACYearではなく、 ACBusinessNo となるはずです。
また、写真を良くみると、1111111 になってします。VLOOKUP関数では、表示上同じでも、数値と文字列が異なる場合、エラーとなってしまいます。受給者証番号は、文字列なのか数値なのか?その定義がとても重要になってきます。セルの書式設定を確認しましょう。

2.VLOOKUPの引数の範囲の引数が、間違っている(A列から指定)
写真の映像を見る限り、受給者証番号がある列は、B列です。よって、"B5:L1000"のように指定します。

3.変数 ACBusinessNo に値を代入していない。
正しいコードを書いても、引数に何も代入しなければ、マクロは動きません。
ACBusinessNoに受給者証番号を代入するコードを初めに書きましょう。
もしかすると、代入しているかもしれませんが・・・

4.VLOOKUPの最後の引数 検索方法を指定していない。
このようなシステムでは、曖昧は許されません。完全一致が条件となります。その為にも、最後の引数 検索方法に FALSE を設定するようにしましょう。

5. エラー回避について
このような場合、結果が正常なのか異常なのか不安になる場合があります。
もし、入力した受給者証番号がテーブルに存在しない場合の処置をした方が良いと思います。

全く異なるなりますが、即席のサンプルで実行した例を載せてみます。
参考にして、自分で考え、作り上げて下さい。

VBA

1Sub test01() 2 Dim x As Long 3 Dim i As Long 4 5 x = 0 6 Range("G2").Activate 7 Do Until ActiveCell.Offset(x, 0).Value = "" 8RE1: 9 If Not IsNumeric(ActiveCell.Offset(x, 0).Value) Then GoTo ERR1 10 For i = 2 To 5 11 On Error GoTo ERR1 12 ActiveCell.Offset(x, i - 1).Value = Application.WorksheetFunction.VLookup(ActiveCell.Offset(x, 0).Value, Range("名簿"), i, False) 13 Next i 14 x = x + 1 15 Loop 16 Exit Sub 17 18ERR1: 19 ActiveCell.Offset(x, 1).Value = "該当なし" 20 x = x + 1 21 GoTo RE1 22End Sub

イメージ説明

最後になりますが、何でもVBAではなく、関数や機能も組み合わせて、システムを構築することも考えると、もっとスマートにできるかもです。

投稿2018/12/24 07:40

kai_keitai

総合スコア344

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

0

一時的に
On Error Resume Nextと
On Error GoTo 0
をコメントにして実行してみてはどうでしょうか?
あとVLOOKUPを実行した後どのような処理をしているでしょうか?
追記 16:00
一旦VBAを離れて、Excelで考えます。

行列ABCDEF
1
2
3
4登録番号受給者証番号所属先氏名所属先事業者番号郵便番号
531111111111日本太郎1234567890444-4444

検索したい 受給者証番号 111111111

今の検索条件=VLOOKUP(111111111,A5:L1000,4);
これだと登録番号で検索になるため、#N/Aとなります。
=VLOOKUP(111111111,B5:L1000,3);
これで受給者証番号で検索となります。

まずはこれで正しいデータが取得できるかやってみて下さい。

投稿2016/12/14 00:29

編集2016/12/14 07:14
date

総合スコア1820

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

rura

2016/12/14 01:05

そちらを取っ払うとエラーが出てしまいます。
torisan

2016/12/14 01:36

察するに エラーを出してそのエラーの内容から原因を追っていけ、 という意味だと思うのですが。。
date

2016/12/14 04:00 編集

エラーになるということはVLOOKUPでデータが取得できなかったということになるので、VLOOKUPを見直してみて下さい。 またここでエラーになると、TCBsNum には何もデータは入りません ちょっと修正しますと、A5からA1000までにACYearが無いとエラーになります。 Sheet1はシート名が決まっているなら Sheets("シート名").Range("A5:L1000") などにしてみてはどうですか
rura

2016/12/14 04:16

出されているエラーは下記の通りです。 実行時エラー ”1004” ワークシートファンクションクラスのVLOOKUPプロパティを取得できません。
date

2016/12/14 04:21

ACYearのデータがない場合もありますか? ACYearのデータがA5からA1000にあるか確認してください。
date

2016/12/14 04:26

話が少し変わりますが、たぶんスマホなどで画像を撮っていると思われるので windows10なら PrintScreenかSnipping Toolで画面がキャプチャできるのでその方がいい。
date

2016/12/14 05:52

ACYEARは登録件数ですか? おそらく受給者番号だと思われますが、その場合だと B列からにしないとエラーです。 WorksheetFunction.VLookup(ACYear, Sheet1.Range("B5:L1000"), 3)
rura

2016/12/14 06:32

B列の受給者証番号をフォームで検索してます。
guest

0

画像が見えにくいです。
まず、vlookup関数の使い方を見ていきましょう。

TCBsNum = WorksheetFunction.VLookup(ACYear, Sheet1.Range("A5:L1000"), 4)

この場合、
1.ACYearの値をキー(検索キー)としてA列に検索キーが存在するかどうかを探します。
2.A列に検索キーが存在した場合(該当行が判明します)、範囲A5からL1000の中でD列の該当行にある値を返します。

検索キーに該当するものが存在しない場合、検索キーに近いものが選択されます。(4番目のパラメータになにも指定しない場合)

投稿2016/12/13 11:40

granfa_yuzo

総合スコア356

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.51%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問