Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。

  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
839 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
▼ 【78005】VLOOKUP関数の検索値を変数にする方法 真田● 16/3/4(金) 18:10 質問[未読]
【78006】Re:VLOOKUP関数の検索値を変数にする方法 β 16/3/4(金) 21:17 発言[未読]
【78012】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/6(日) 13:49 質問[未読]
【78013】Re:VLOOKUP関数の検索値を変数にする方法 マナ 16/3/6(日) 14:03 発言[未読]
【78014】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/6(日) 16:20 回答[未読]
【78015】Re:VLOOKUP関数の検索値を変数にする方法 マナ 16/3/6(日) 16:33 発言[未読]
【78017】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/6(日) 17:40 質問[未読]
【78018】Re:VLOOKUP関数の検索値を変数にする方法 マナ 16/3/6(日) 18:52 発言[未読]
【78023】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/7(月) 16:36 質問[未読]
【78025】Re:VLOOKUP関数の検索値を変数にする方法 マナ 16/3/7(月) 20:59 発言[未読]
【78032】Re:VLOOKUP関数の検索値を変数にする方法 β 16/3/8(火) 11:25 発言[未読]
【78033】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/8(火) 17:41 質問[未読]
【78034】Re:VLOOKUP関数の検索値を変数にする方法 β 16/3/8(火) 19:26 発言[未読]
【78035】Re:VLOOKUP関数の検索値を変数にする方法 ichinose 16/3/9(水) 6:59 発言[未読]
【78036】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/9(水) 18:54 お礼[未読]
【78019】Re:VLOOKUP関数の検索値を変数にする方法 β 16/3/6(日) 18:53 発言[未読]
【78016】Re:VLOOKUP関数の検索値を変数にする方法 真田● 16/3/6(日) 17:35 質問[未読]
【78005】VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/4(金) 18:10 -
引用なし
パスワード
   VLOOKUP関数の検索値を変数にするには、どうしたらよいのでしょうか？
色々調べたのですが分かりません。

検索対象はI列で、検索データは文字列になります
【78006】Re:VLOOKUP関数の検索値を変数にする方法
発言  β  - 16/3/4(金) 21:17 -
引用なし
パスワード
   ▼真田● さん：

ここはＶＢＡの板ですから、ＶＢＡでいいのですね？

単純に　固定値　を　変数にすればいいのですが？

結果 = WorksheetFunction.VLookUp(変数,Range("I1:J10"),2,False) とか。

ただし、見つからなかった場合にエラーになってしまいますので、
エラー対策（エラートラップ等）をほどこすか、あるいは（こちらがおすすめですが）

Variant型変数 = Application.VLookUp(変数,Range("I1:J10"),2,False)
If IsError(Variant型変数) Then
　 見つからなかった場合の処理
Else
　 見つかった場合の処理
End If

このようにしておくことが必要ですね。
【78012】Re:VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/6(日) 13:49 -
引用なし
パスワード
   I列をループして検索したいと思っております

I2のデータだけを検索するのであれば下の関数で大丈夫ですが

VLOOKUP(I2,[商品コード.xlsx]Sheet1!A$1:B$394,2,FALSE)

毎日データが変化するI列全体を検索したいので
検索地を変数で設定したいと思っております。

残念ながら、回答していただいたVBAは理解できないのですが

Variant型変数 = Application.VLookUp(変数,Range("I1:J10"),2,False)
If IsError(Variant型変数) Then
　 見つからなかった場合の処理
Else
　 見つかった場合の処理
End If

は、このまま使えるVBAなのでしょうか？

お手間取らせて申し訳ございません。

結果は、C列に反映させたいのですが
併せてアドバイスいただけると助かります。
【78013】Re:VLOOKUP関数の検索値を変数にする方法
発言  マナ  - 16/3/6(日) 14:03 -
引用なし
パスワード
   ▼真田● さん：
>I列をループして検索したいと思っております
>I2のデータだけを検索するのであれば下の関数で大丈夫ですが
>VLOOKUP(I2,[商品コード.xlsx]Sheet1!A$1:B$394,2,FALSE)
>

>結果は、C列に反映させたいのですが


I2の検索結果をC2
I3の検索結果をC3
I4の検索結果をC4

という意味でしょうか。
ならば、C列にその式を、フィルコピーするだけでは？
【78014】Re:VLOOKUP関数の検索値を変数にする方法
回答  真田● E-MAIL  - 16/3/6(日) 16:20 -
引用なし
パスワード
   想定できるデータの数以上に
C列に数式をコピーして入れておけばマクロの記録でも対応できますが
マクロの記録で処理すると原因不明のエラーが出やすいので
使用は控えたいところです。
【78015】Re:VLOOKUP関数の検索値を変数にする方法
発言  マナ  - 16/3/6(日) 16:33 -
引用なし
パスワード
   ▼真田● さん：
>想定できるデータの数以上に
>C列に数式をコピーして入れておけばマクロの記録でも対応できますが

予め数式を入れておけば、マクロはいっさい不要だと思いますが。
【78016】Re:VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/6(日) 17:35 -
引用なし
パスワード
   β さん

教えていただいた内容、だいたい分かってきました。

この数式に問題があるでしょうか?


Variant型変数 = Application.VLookUp(商品コード,[商品コード.xlsx]Sheet1!A$1:B$394,2,FALSE)
　　Dim 商品コード As String
　　商品コード = Range("I2", Range("I" & Rows.Count).End(xlUp)).Value


If IsError(Variant型変数) Then
Variant型変数=N/S
Else
検索結果=Variant型変数

　　検索結果 = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value

End If
【78017】Re:VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/6(日) 17:40 -
引用なし
パスワード
   予め数式を入れるというのは
βさんへの質問の中で書いた

検索結果=Variant型変数

検索結果 = Range("C2", Range("C" & Rows.Count).End(xlUp)).Value

のような数式の事を指しているのでしょうか？

数式の正誤のアドバイスも兼ねて、
βさんへの一番新しい質問を見ていただけると助かります。
【78018】Re:VLOOKUP関数の検索値を変数にする方法
発言  マナ  - 16/3/6(日) 18:52 -
引用なし
パスワード
   ▼真田● さん：

ご自身でも仰られたとおりC列に数式を、想定される行数分以上入力しておけば
マクロが不要ではないかという意味です。

マクロで変数を使うとするとこんな感じでしょうか。
商品コード.xlsxは同時に開いているという前提です。

Sub test()
　　Dim 検索値 As Range
　　Dim 検索範囲 As Range
　　Dim 検索結果
　　
　　With ActiveSheet
　　　　.Range("C2", .Range("C" & .Rows.Count).End(xlUp)).ClearContents
　　　　Set 検索値 = .Range("I2", .Range("I" & .Rows.Count).End(xlUp))
　　End With
　　
　　Set 検索範囲 = Workbooks("商品コード.xlsx").Worksheets("Sheet1").Range("A1:B394")
　　
　　検索結果 = Application.VLookup(検索値, 検索範囲, 2, False)
　　検索値.Offset(, -6).Value = 検索結果

End Sub
【78019】Re:VLOOKUP関数の検索値を変数にする方法
発言  β  - 16/3/6(日) 18:53 -
引用なし
パスワード
   ▼真田● さん：

>は、このまま使えるVBAなのでしょうか？

いえいえ、サンプルというかコードの雛形というか、考え方です。

>マクロの記録で処理すると原因不明のエラーが出やすいので
>使用は控えたいところです。

そのまま使えることもありますが、それは、例外かもですね。
通常は、固定領域であったり、固定のシートになっていたり
なによりも Select とか Selection がてんこ盛りに生成されますから
お化粧直しをして使います。

でも、コードの雛形を提供してくれるありがたい機能ですよ。

【このまま使えるか】というコメントも、その一端ですけど、
雛形を自分の要件に合わせてチューニングするようにしていかないと
【そのままコピペで自分の要件をカバーして満足すり結果が得られるコード】
だけを求めると、なかなか身につきませんよ。

ということで、以下も【サンプル】です。
新規ブックで試してください。
まず、MakeData を実行してください。I列、J列にリストを作成します。

次にSampleを実行してください。
I列、J列のリスト範囲を自動的に判定取得して、その領域に対して
変数 s で検索をかけています。
最初はリスト内に存在する値、次にリスト内に存在しない値で
VLOOKUPをかけています。

Sub MakeData()
　　Dim i As Long
　　
　　Cells.Clear
　　
　　For i = 1 To 100
　　　　Cells(i, "I").Value = "a" & i
　　　　Cells(i, "J").Value = "x" & i
　　Next
　　　　
End Sub

Sub Sample()
　　Dim z As Variant
　　Dim s As String
　　
　　s = "a60"
　　
　　z = Application.VLookup(s, Range("I1", Range("I" & Rows.Count).End(xlUp)).Resize(, 2), 2, False)
　　If IsError(z) Then
　　　　MsgBox s & " は見つかりません"
　　Else
　　　　MsgBox "結果は " & z & " でした"
　　End If
　　
　　
　　s = "a999"
　　
　　z = Application.VLookup(s, Range("I1", Range("I" & Rows.Count).End(xlUp)).Resize(, 2), 2, False)
　　If IsError(z) Then
　　　　MsgBox s & " は見つかりません"
　　Else
　　　　MsgBox "結果は " & z & " でした"
　　End If
　　
End Sub
【78023】Re:VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/7(月) 16:36 -
引用なし
パスワード
   ▼マナ さん：

ありがとうございます。
完璧でした。

一つ質問なのですが、エラーになった時の処理は
この数式にはどの部分に入っているのでしょうか?


ついでに教えていただけたら幸いですが
VBAではVLOOLUPは使わない方が良いと言ってる人がおいようですが
同じ処理を、エラーの少ない方法で処理することは可能なのでしょうか?
【78025】Re:VLOOKUP関数の検索値を変数にする方法
発言  マナ  - 16/3/7(月) 20:59 -
引用なし
パスワード
   ▼真田● さん：

>エラーになった時の処理

βさんの最初の回答にあるように
WorksheetFunction.VLookUp
だと、マクロがエラーで止まっちゃうので、
Application.VLookUp
を使用しているのがポイントです。

他の掲示板ですが
ht　tp://excelfactory.net/excelboard/excelvba/excel.cgi?mode=all&namber=174828&rev=0
ここのappleさんの　2016/03/04(22:02　の説明を読んでみてください


>VBAではVLOOLUPは使わない方が良いと言ってる人がおいようですが

ごめんなさい。わたしにはわかりません。
というか、vlookupが使えるものなら積極的に使います。
vlookupに限らず、ワークシート関数を使うことにためらいはありません。

でも、先のコードが希望の動作であるなら、
やはり今回のようなケースでマクロを使う必要はないと思います。

コードを書いてみたのは、appleさんの説明を試してみただけで、
何度も申し上げていますが、わたしならC列に予めvlookupを入力しておくと思います。
【78032】Re:VLOOKUP関数の検索値を変数にする方法
発言  β  - 16/3/8(火) 11:25 -
引用なし
パスワード
   ▼真田● さん：

>VBAではVLOOLUPは使わない方が良いと言ってる人がおいようですが

想像ですけど、たぶん

結果1 =Application.VLOOKUP(検索文字列,検索領域,2,FALSE)
結果2 =Application.VLOOKUP(検索文字列,検索領域,3,FALSE)
結果3 =Application.VLOOKUP(検索文字列,検索領域,5,FALSE)

といったようなコード。
同じ検索文字列ですね。VLOOKUPは検索領域に、その検索文字列があるかどうかを
チェックしますよね。
そうすると、↑の３つのコード、同じ検索を3回行っていることになります。
取り出すものがかわるだけですよね。

こういったときには VLOOKUP を使わず、MATCH で検索し、ヒットすれば
その行の該当の列の値を取得するほうが、処理効率はいいですよね。

そういうことを言っているのだと思います。

それと、

>この数式に問題があるでしょうか?

まず、検索領域って、別ブックだったのですか？
いずれにしても、その検索領域の記述は間違っています。
アップされたコードのそれは【セルの数式】での記述スタイルですね。
ＶＢＡで使う場合は、ＶＢＡとしての記述（Rangeオブジェクトを使って記述）することが
必要です。

それと、検索文字列の商分コードですけど、検索したあとセット？
検索前にセットして検索することが必要ですよ。
【78033】Re:VLOOKUP関数の検索値を変数にする方法
質問  真田● E-MAIL  - 16/3/8(火) 17:41 -
引用なし
パスワード
   ▼β さん：
MATCH関数というのがあるのですか
奥が深いですね
後で、調べてみます。

別ファイルをRangeで指定する場合は、下の書き方で正解なのでしょうか?

Range("[商品コード.xlsx]Sheet1!A$1:B$394"),
【78034】Re:VLOOKUP関数の検索値を変数にする方法
発言  β  - 16/3/8(火) 19:26 -
引用なし
パスワード
   ▼真田● さん：

Workbooks("商品コード.xlsx").Sheets("Sheet1").Range("A1:B394")

といった記述をします。
（ブックが開かれているという前提です）
【78035】Re:VLOOKUP関数の検索値を変数にする方法
発言  ichinose  - 16/3/9(水) 6:59 -
引用なし
パスワード
   ▼真田● さん：
>▼β さん：
>MATCH関数というのがあるのですか
>奥が深いですね
>後で、調べてみます。
>
>別ファイルをRangeで指定する場合は、下の書き方で正解なのでしょうか?
>
>Range("[商品コード.xlsx]Sheet1!A$1:B$394"),
標準モジュールでは、使用頻度は低いですが間違いではありません。

シートモジュールでは、エラーに成ります。

どこでも通用させるには


Application.Range("[商品コード.xlsx]Sheet1!A$1:B$394")
こんな記述です。


βさんの記述が一般的ですけど・・・。
【78036】Re:VLOOKUP関数の検索値を変数にする方法
お礼  真田● E-MAIL  - 16/3/9(水) 18:54 -
引用なし
パスワード
   β さん、ichinose さん、マナさん

ありがとうございます。

今夜、確認してみます。
  新規投稿 ┃ツリー表示 ┃一覧表示 ┃トピック表示 ┃検索 ┃設定 ┃過去ログ ┃ホーム  
839 / 13645 ﾂﾘｰ ←次へ　｜　前へ→
ページ：  ┃  記事番号：
2610219
(SS)C-BOARD v3.8 is Free