[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『vlookupの検索値を最終行まで取得したい』（miffy）winXP ex03
いつも同じような質問ですみません… 今、"情報取得"シートのA列を検索値に、"情報取得"シートのB列には"基本情報"シートの2列目を、 C列には"基本情報"の4列目を、D列には"基本情報"の9列目を表示したいと思っています。
A列はA3セル以下に以前教えていただいたマクロを実行する都度必要なデータが抽出されるようになっています。 以下のコードですと、検索値がA3セルのままになってしまい、「検索値」のところを いじってみたのですがうまくいきません。
また、C列D列と同様にコードを打たなくても良い方法があれば助かります。 よろしくお願いします。
Sub 苦戦中()
Dim 範囲 As Range
Set 範囲 = Worksheets("基本情報").Range("A:I")
列番号 = 2
検索値 = Worksheets("情報取得").Range("A3")
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
irow = Range("b" & Rows.Count).End(xlUp).Row
Range("b" & irow + 1).Value = 値
End Sub
>いじってみたのですがうまくいきません。
がんばっているようなので、ヒントをｂ
「列番号」に「2」が入っているから
>"基本情報"シートの2列目
になるのはわかりますよね？
>"基本情報"の4列目
になる為には、どうすればいいでしょうか？
また、同じく
>"基本情報"の9列目
にする為には？
>Range("b" & irow + 1).Value = 値
ここで「"b"」となっている為、「B列」に表示しているのはわかりますよね？
>C列には
>D列には
そうすると、何処を変更すればよいでしょう？
ループすることで、コンパクトに出来そうですが
そのまま命令文を増やすことからはじめてみませんか？
がんばってくださいね＾＾
（キリキ）（〃⌒o⌒)b
マクロで数式を埋め込んで 値化するのでは駄目なのでしょうか？
たとえば、 B3:B10セルを選択して =VLOOKUP($A3,基本情報!$A:$I,2,FALSE) を入力して Ctrl + Enter
C3:C10セルを選択して =VLOOKUP($A3,基本情報!$A:$I,4,FALSE) を入力して Ctrl + Enter
B3:C10セルを選択してコピー→値貼り付け
この手順をマクロの記録にとれば、 マクロを実行した時に B3:C10 の値が更新されることになります。
実際は、10行目までではなく データの最終範囲までなので その点は変更が必要です。 また、記録にとったままだと、画面がちらちらするので その辺りも変更しておくのが良いと思いますが。
(HANA)
>キリキさん レスありがとうございます。 以下のようなものは出来ているのですが… 「検索値」であるA3を下の最終行まで取得することができません…。
Sub 苦戦中()
Dim 範囲 As Range
Set 範囲 = Worksheets("基本情報").Range("A:I")
列番号 = 2
検索値 = Worksheets("情報取得").Range("A3")
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
irow = Range("b" & Rows.Count).End(xlUp).Row
Range("b" & irow + 1).Value = 値
Set 範囲 = Worksheets("基本情報").Range("A:I")
列番号 = 4
検索値 = Worksheets("情報取得").Range("A3")
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
irow = Range("c" & Rows.Count).End(xlUp).Row
Range("c" & irow + 1).Value = 値
Set 範囲 = Worksheets("基本情報").Range("A:I")
列番号 = 9
検索値 = Worksheets("情報取得").Range("A3")
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
irow = Range("d" & Rows.Count).End(xlUp).Row
Range("d" & irow + 1).Value = 値
End Sub
>HANAさん レスありがとうございます。 コピーと貼り付けをマクロに…ですか、思いつきませんでした。 しかし情報が日々増えていき、多数の人に配布することになる（ことを目標に作っている） ため、その「最終範囲まで」が場合によってまちまちです。
検索値＝のあとで最終行を指定するようなことは難しいのでしょうか…
(miffy)
＞その「最終範囲まで」が場合によってまちまちです。 ですから、その部分は変更が必要ですね。
数式を埋め込んで値化・・・で良いなら まずはマクロの記録をやってみて下さい。
出来たコードをこちらに載せてください。
ちなみに、最終行は
＞＞irow = Range("b" & Rows.Count).End(xlUp).Row
で求められていますね？
（今度は、A列を見る事になりますが。）
(HANA)
ちょっと時間が無いので簡単に書いておきます。
たとえば、
B3:B10セルを選択して
=VLOOKUP($A3,基本情報!$A:$I,2,FALSE)
を入力して Ctrl + Enter
すると、
Range("B3:B10").Select
Selection.FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)"
こんなコードが出来ます。
2行目の Selection とは、1行目の Range("B3:B10") の事なので
一行にまとめて
Range("B3:B10").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)"
と出来ます。
実際は、B3:B10の範囲に数式を入れれば良いのではなく
A列の最後の行まで 数式を入れる必要があります。
A列の入力がある最後の行は
irow = Range("A" & Rows.Count).End(xlUp).Row
で求められるので、B3 から B列の irow行の範囲に 数式を入れれば良いです。
Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)"
もしも irowが「10」だったら "B3:B" & 10 → B3:B10 に成りますね？
C列には、VLOOKUP関数の列が「4」になった数式が入れば良いので
Range("C3:C" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,4,FALSE)"
もしもこの数式の変更場所が分からなければ この式も記録にとってみれば良いと思います。
少し難しいですが、「基本情報!C1:C9」は、
基本情報シートのC1:C9セルの事ではありませんので、混同なさいません様に。
それから、
B3:C10セルを選択してコピー→値貼り付け
すると
Range("B3:C10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
こんなコードが出来ます。
2行目と3行目の Selection はやはり B3:C10 の事なので
Range("B3:C10").Copy
Range("B3:C10").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
これまた C10 の所の 10 → irow ですので 同じ様に変更します。
あとは、実行後にどうしておきたいか コピーモードは解除しておきたい 貼り付けた範囲が選択されているので どこか一つのセルを選択して終わりたい 等ありましたら、マクロの記録を参考に付け加えて行ってください。
コピーモードの解除は Esc で出来ますね。
実際は、D列まであるので D列の処理は付け加えてください。
また、変数「irow」を使うので 変数の宣言
Dim irow As Long
もお忘れなく書いておいてください。
まずは駆け足で。。。 読んだだけでは分かりにくいと思いますので 実際にマクロの記録をとって 出来たコードとにらめっこしながら 一つずつ納得して行ってもらえればと思います。
(HANA)
それでは、σ(^o^;)はコードに説明をつけてみましょうかねｂ
Sub 苦戦中()
'「範囲」という Rangeオブジェクトの変数宣言
Dim 範囲 As Range
'変数「範囲」に、基本情報シートの A列からI列をセット
Set 範囲 = Worksheets("基本情報").Range("A:I")
'変数「列番号」に、2 を格納
列番号 = 2
'変数「検索値」に、情報取得シートの、A3 に入っている情報（値）を格納
検索値 = Worksheets("情報取得").Range("A3")
'変数「値」に、VLOOKUP関数を使用し返ってきた値を格納
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
'変数「irow」に、B列 の最終行を取得
'※ B列 の一番下段のセルから、Ctrl + ↑をした時のセルの行番号と同じ
irow = Range("b" & Rows.Count).End(xlUp).Row
'B列 の irow（最終行）に、+1 した場所（最終行の一つ下のセル）に、「値」を返す
Range("b" & irow + 1).Value = 値
End Sub
少し理解が進むとうれしいです＾＾
（キリキ）(〃⌒o⌒)b
>HANAさん 遅い時間に丁寧にありがとうございます。 とりあえず、B列だけでも、と以下のコードにしてみましたが「検索値」の行でエラーになってしまいます。 ”実行時エラー1004 アプリケーション定義またはオブジェクト定義のエラーです” というメッセージです。 たくさん書いていただいたのに理解できておらず申し訳ありません…
Sub 苦戦中()
Dim 範囲 As Range
Dim irow As Long
Set 範囲 = Worksheets("基本情報").Range("A:I")
列番号 = 2
検索値 = Worksheets("情報取得").Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)" 'エラー箇所
Range("B3:B" & irow).Copy 'D列まで作ったら("B3:D" & irow) にする？
Range("B3:B" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
値 = Application.WorksheetFunction.VLookup(検索値, 範囲, 列番号, False)
irow = Range("b" & Rows.Count).End(xlUp).Row
Range("b" & irow + 1).Value = 値
End Sub
(miffy)
全体の処理は見ていませんが、irow に値を入れないまま、使用しているようですね。
それではアドレスが存在しないので、当該のエラーになります。
irow = Range("b" & Rows.Count).End(xlUp).Row
は
Dim irow As Long
の直後で実行してみてどうでしょうか。
(Mook)
今日は、社外に出る予定だったのですが、予定が変わりました。。。 （行程が一日ずれたので。。。）
まずはマクロの記録をとって下さい。 記録したマクロを実行して、動きを確認して下さい。 そこから、一行に纏める部分。書き替える部分 を 変更して下さい。
すると
＞検索値 = Worksheets("情報取得").Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)"
なんて事にはならないと思いますよ。
現在作っておられるマクロとは 別物として考えて下さい。
「データ範囲の最終行をirowに入れる」と言う事以外は 別の物だと思って頂ければ良いと思います。
(HANA)
>Mookさん HANAさん レスありがとうございます。 HANAさんのおっしゃるとおりマクロの記録で"「検索値」が難しい"の意味がようやくわかりました。 以下のコードで動きました。ありがとうございました。
Sub 苦戦中()
Dim irow As Long
irow = Range("a" & Rows.Count).End(xlUp).Row
Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)"
Range("B3:B" & irow).Copy
Range("B3:B" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Range("c3:c" & irow).FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)"
Range("c3:c" & irow).Copy
Range("c3:c" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,Transpose:=False
Range("d3:d" & irow).FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)"
Range("d3:d" & irow).Copy
Range("d3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub
HANAさんのおっしゃっているとおり、最後のコピーモードの解除のヒントをいただけないでしょうか。
Escボタンを押してマクロの記録をしてもコードがなく、Range("b3").Activate を最後に足しても
コピーモードはそのままで…
(miffy)
出来ましたか 良かったです。 丁度 「難しい様なら、記録をとった状態のコードを載せて貰えば良いですが」 と書こうと思った所でした。
作製されたコードに関して もう少し書かせて下さい。
現在のコードは B列に数式を埋め込む B列の範囲をコピー・・・・・・・◎ B列の範囲に値貼り付け・・・・・◎
C列に数式を埋め込む C列の範囲をコピー・・・・・・・◎ C列の範囲に値貼り付け・・・・・◎
DB列に数式を埋め込む D列の範囲をコピー・・・・・・・◎ D列の範囲に値貼り付け・・・・・◎ と成っています。
でも、B;D列は連続しているので B列に数式を埋め込む C列に数式を埋め込む D列の数式を埋め込む
B:D列の範囲をコピー・・・・・◎ B:D列の範囲に値貼り付け・・・◎ とやっても良く有りませんか？
セルへのアクセス回数が減るので、寧ろすっきりすると思います。
＞Escボタンを押してマクロの記録をしてもコードがなく コピーモードの状態で、 Escボタンを押してみて下さい。 （コピーモードでない状態で Escボタンを押しても何も記録されません。）
例えば
１．A1セルを選択
２．コピー
３．Esc を押す
です。
Range("A1").Select
Selection.Copy
Application.CutCopyMode = False
こんなコードが出来ますが、コピーモードが解除されるのは
３の手順をやった時なので、3行目の部分が該当しますね。
(HANA)
>HANAさん できましたーー！非常にわかりやすい説明で大変勉強になりました。
Sub 苦戦中()
Dim irow As Long
irow = Range("a" & Rows.Count).End(xlUp).Row
Range("B3:B" & irow).FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)"
Range("c3:c" & irow).FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)"
Range("d3:d" & irow).FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)"
Range("b3:d" & irow).Copy
Range("b3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
End Sub
こんなにスッキリするものなんですね！ 明らかに速くなりました。早速A列のデータ抽出用マクロの中に入れて動かしたいと思います。
お仕事のお忙しい中で何度もありがとうございました。 (miffy)
出来ましたか。良かったです。
せっかくなので B3セルに =VLOOKUP($A3,基本情報!$A:$I,2,FALSE) C3セルに =VLOOKUP($A3,基本情報!$A:$I,4,FALSE)
B4セルに =VLOOKUP(A3,基本情報!A:I,2,FALSE) C4セルに =VLOOKUP(A3,基本情報!A:I,4,FALSE)
とした時に マクロの記録で作製される数式の違いを 確認してみて頂けると良いと思います。
絶対参照の数式を入力した場合の 数式の変化する場所、しない場所。 相対参照で入力した場合の 数式の変化する場所、しない場所。
それから、これから先マクロを書いていく時に
便利に使えるかもしれないので。。。
Range("b3:d" & irow).Copy
Range("b3:d" & irow).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
この3行の変わりに
Range("b3:d" & irow).Value = Range("b3:d" & irow).Value
この1行も試してみて下さい。
(HANA)
>HANAさん 絶対参照と相対参照試してみました。絶対参照だと[]がなくなるようですね… よくわからずすみません
copyのところも、セルに関数を残さないためにやっていたことなんですね。 ようやくわかりました。
いろいろ教えていただきありがとうございます。
(miffy)
＞絶対参照と相対参照試してみました。絶対参照だと[]がなくなるようですね… そうなんです。 基本的には 数式を埋め込もうと思った時に マクロの記録にとって エクセルにVBAでの数式を書いてもらって 流用すればよいと思いますが 雰囲気だけでもわかっておいてもらえると 便利だと思うので 少し イメージを書いてみます。
たとえば、B3セルに ＞=VLOOKUP($A3,基本情報!$A:$I,2,FALSE) の式を入れて、D3までフィルドラッグした場合 目的の数式にするためには C3,D3の数式内の 列番号を表す部分を 4,9に変えますね。
マクロの記録を取った場合も
Range("B3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,2,FALSE)"
Range("C3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,4,FALSE)"
Range("D3").FormulaR1C1 = "=VLOOKUP(RC1,基本情報!C1:C9,9,FALSE)"
の様に、列番号を表す部分だけが違う式が記録されたと思います。
たとえば、B3セルに ＞=VLOOKUP(A3,基本情報!A:I,2,FALSE) の式を入れて、D3までフィルドラッグした場合 目的の数式にするためには A3 → B3,C3になっているので A3,A3に戻す A:I → B:J,C:K になっているので A:I,A:Iに戻す 列番号を表す部分を、4,9に変える といった変更が必要になります。
マクロの記録を取った場合も
Range("B3").FormulaR1C1 = "=VLOOKUP(RC[-1],基本情報!C[-1]:C[7],2,FALSE)"
Range("C3").FormulaR1C1 = "=VLOOKUP(RC[-2],基本情報!C[-2]:C[6],4,FALSE)"
Range("D3").FormulaR1C1 = "=VLOOKUP(RC[-3],基本情報!C[-3]:C[5],9,FALSE)"
3箇所が違う式が記録されていますね。
数式を記録にとる際も、絶対参照にしていたものは 絶対参照で 相対参照にしていたものは 相対参照で 記録されるので そのあたりも考えながら記録にとっていくと ちょっとした変更なら 簡単にできる様になると思います。
もしもコード内でも「=VLOOKUP($A3,基本情報!$A:$I,2,FALSE)」と書けたら
後で見た時もわかりやすいですよね。。。
それは、基本的には ↓な感じで書けば良いのですが
Range("B3").Formula = "=VLOOKUP($A3,基本情報!$A:$I,2,FALSE)"
数式によっては単純に「""」でくくって入れただけではうまくいかない場合もありますので
その際は、マクロの記録も参考にしてください。
たとえば、=IF(A1="","",B1)といった式は
Range("C1").Formula = "=IF(A1="""","""",B1)"
の様になります。記録にとると
ActiveCell.FormulaR1C1 = "=IF(RC[-2]="""","""",RC[-1])"
「"」が二つずつになっているので「二つずつにするんだ」と推測して修正する。
実は、今回の様なパターンであれば マクロの記録だけでも ＞その「最終範囲まで」が場合によってまちまちです。 をクリアするマクロを作る事が出来ます。
でも、この度コードを作成した様に 基本部分はマクロの記録で作って ポイント部分だけ書きかえる 同時に 不要部分は削除する のは、簡単だったと思いませんか？
マクロの記録だけでは出来ない事もありますが コードを作成する際に マクロの記録はとても役に立つと思うので 上手に活用してみてもらえるのが良いと思います。
ちなみに、「コピー後値貼り付け」と「○○.Value = ○○.Value」は 結果が少し変わってくる場合がありますので その様に記憶に留めておいて頂ければと思います。
なお、最初に載せられたコードを使用するには ループ処理が必要になってきます。
これは、マクロの記録ではどうしても出来ない事です。 でも、わかるようになると 色々な事が簡単に出来る様になると思います。
今回の場合のループは割と簡単な方だと思いますので この機会に やっておいてみられてはどうでしょう？
たとえば、↓のマクロは ループ処理で B3:B5に 3～5 の値を入れます。
Sub ループ1()
Dim i As Long
For i = 3 To 5
Range("B" & i).Value = i
Next
End Sub
(HANA)
>HANAさん 本当に細かくありがとうございます！！ 絶対参照の部分、大変便利に使えそうです。 実は「こういうことがしたい」ということばかりが先に立ってvbaの基本を理解せずに組んでいるので、 マクロの記録機能は最初の最初だけ「ふ～んマクロってこんなことができるなんてすごいなぁ」という 程度で「でも編集が大変そうだなぁ」で避けていました。 HANAさんのご指導で今後は記録も大いに活用していきたいと思います。
また、loopも、今諦めの境地に至っている「やりたいこと」があるのですが、それには絶対必要に なるようなんですが、自分で作り出したら無限loopに陥り、このHPでそこを解決したらまたその処理の中で 他にloop（もしくは上記のFor～nextで出来るのかも）を使わないといけないことがわかり、大苦戦している ところです。 HANAさんに教えていただいた基本を元にもう一度検証してみたいと思います！ ありがとうございました。 (miffy)
私はかなりイイカゲンなコードを書くので Do ～ Loop とか Loop ～ 等のループ処理は まさに 無限loop に成ってしまうので For ～ Next の方が良く使います。
今回の物もループ処理で処理出来ますので 例題として挑戦してみられてはどうでしょう。 （本番の前の練習です。） せっかくキリキさんがコードに解説を付けてくれた事ですし。。。
(HANA)
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.