こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

エクセルVBA VLOOKUPについて

エクセル VBA初心者です。

関数でのVLOOKUPをVBAで作りたいのですが、上手くいきません。

あらかじめ、Sheet2の1から300行までに

A列  / B列
商品名 / 商品コード

が入力されています。(名前の定義=商品コード)

Sheet1にユーザーフォームを利用して、データを書き込んだ後、
B列に商品名が書き込まれると、
A列に商品コードが表示されるようにしたいと考えています。

A列に
=IF(B2="","",VLOOKUP(B2,商品コード,2,FALSE))
と入力していたのですが、

VBAでIfを使って出来ないかと考えてみたのですが、
上手くいきませんでした。

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sRow As Long
Dim sColumn As Long
sRow = ActiveCell.Row
sColumn = ActiveCell.Column
If Cells(sRow, 2).Value = True Then
Cells(sRow, 1).Value = WorksheetFunction.VLookup(Cells(sRow2).Value, Worksheets("Sheet2").Range("A1:B300"), 2, False)
ElseIf Cells(sRow, 2).Value = " " Then
Cells(sRow, 1).Value = " "
End If
End Sub

ご教授いただけないでしょうか?

エクセル2003
WindowsXP

投稿日時 - 2013-08-02 16:17:19

QNo.8202805

困ってます

質問者が選んだベストアンサー

マクロを使おうとしているのですから,

この部分:A
>Sheet1にユーザーフォームを利用して、データを書き込んだ後、

と,こちらの部分:B
>B列に商品名が書き込まれると、

との,実際のエクセルの動作の連係をキチンと「定義づけ」しておかないと,どんなマクロにしたらイイのか困ります。



とりあえず。
Aの部分は思いっきり「無視して」,前後の経緯はどうあれ「B列に商品名が記入されたらA列にコードを記入させる」というだけのマクロを考えてみることにします。


private sub Worksheet_Change(byval Target as excel.range)
 dim h as range
 on error resume next
 application.enableevents = false

’worksheet_changeマクロはどのセルが変わっても反応する
 for each h in application.intersect(target, range("B:B"))

’ また複数のセルを一斉編集した場合も反応するので,その1つずつについて
  if h = "" then
  ’空白であればクリアする
   cells(h.row, "A").clearcontents
  else
  ’そうでなければ計算する(見つからずにエラーが計算される場合も含め)
   cells(h.row, "A") = application.vlookup(h.value, worksheets("Sheet2").range("A1:B300"), 2, false)
  end if
 next
 application.enableevents = true
end sub

投稿日時 - 2013-08-02 16:49:26

お礼

参考にさせていただき、無事できました。
ありがとうございます。

Aの部分は無視していただいて正解です。

「B列に商品名が記入されたらA列にコードを記入させる」
が知りたかったことです。

みなさんに結局言われていますが、
VBAで作成する意味が無いと言われると思い、
「他の部分はVBAで作ってます」とアピールしただけです。
必要なかったですね。

詳しい説明、ありがとうございました。

投稿日時 - 2013-08-03 12:26:32

ANo.4

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(5)

No.1~4 の方々のご回答は、「一言一句、全て」ごもっともなので、よくご確認ください。

No.1 さんのおっしゃっているマクロ化の必要性についてですが、例えば、1 つのプロシージャ内でいっぺんに行うべき処理がたくさんあって、そのうちの一つとして WorksheetFunction.VLookup メソッドも併せて行うということはあり得ると思います。「WorksheetFunction.VLookup」は、「application.vlookup」と書いてもいいです。

No.4 さんのコードにあるとおり、「""」と「" "」、「Else」と「ElseIf」は異なるので、注意してください。

「Cells(sRow, 2).Value = True」というのは、そのセルの値が「True」もしくは「-1」であれば True になる、という意味の条件式です。そうではなくて、質問者さんの目的のためには、No.4 さんのように

if h = "" then

とするか、逆に、

If h <> "" Then
  cells(h.row, "A") = WorksheetFunction.VLookup(h, Range("商品コード"), 2, 0)
Else
  cells(h.row, "A").clearcontents
End If

とするかの、どちらかです。「""」というのは空白の他に、値が空文字列というケースも該当しますが、ClearContents メソッドを省略しても特に問題ないという場合は、

If h <> "" Then
  cells(h.row, "A") = WorksheetFunction.VLookup(h, Range("商品コード"), 2, 0)
End If

あるいは

If h <> "" Then cells(h.row, "A") = WorksheetFunction.VLookup(h, Range("商品コード"), 2, 0)

でもオッケーです。

なお質問文にある「商品コード」という定義された名前を使ってコードを書くなら、「Range("商品コード")」と記述します。この場合、Sheet1 とか Sheet2 と、あえてオブジェクトを指定する必要はありません。名前の定義の中に既に含まれているので。No.3 さんと同じく、商品コードと商品名の記入の順序が逆な気がして、違和感がバリバリではありますが…。

投稿日時 - 2013-08-03 03:43:09

お礼

始めて2週間ほどのド素人のため、説明不足をお許しください。

無難な「商品」という言葉に置き換えましたが、実際は大きな「商品」の中にA,B,Cなどと細かく分かれており、
それぞれの顧客に対しての自分の仕事を管理したくて作っています。

「商品コード」は、会社の管理しているデータを確認するためには、コードが必要なため、
後付でいいので表示させたいと思いました。

その部分の方法を知りたかったというわけです。

関数でも十分事足りていますが、セル1つに対しての式なので、
数が増えるとコピーしていかなければばらないので、
VBAで作ってあったほうが便利だと思っただけです。

そこまでの説明が必要だと思わなくて、省いてしまいました。

勉強になりました。
ありがとうございます。

投稿日時 - 2013-08-03 12:30:06

ANo.3

あー、もう一つ。
#1さんのご指摘ももっともなのですが・・

そもそも、通常のデータベース的思考でいくと、
「商品コード」を見て「商品名」を返す、だと思いますよ。

#1さんのご指摘も、この指摘も、
「そうしたい理由」というのがどこかにあるのかもしれませんので
安易な否定は避けておきますが・・
まずは「本当にそうしなければいけないのか?」から考えると良いかもしれません。


私も基本的には「この処理は不要」と思いますが、
Targetの使い方は今後のために覚えておいて損は無いですよ。

投稿日時 - 2013-08-02 16:42:38

お礼

Targetの使い方、勉強になりました。

無難な「商品名」という言葉に置き換えましたが、実際は大きな『商品』の中にA,B,Cなどと細かく分かれており、
それぞれの顧客に対しての自分の仕事を管理したくて作っています。

「商品コード」は、会社の管理しているデータを確認するためには、コードが必要なため、
後付でいいので表示させたいと思いました。
だから「商品名」から「商品コード」を返したかったのです。

関数でも十分事足りていますが、セル1つに対しての式なので、
数が増えるとコピーしていかなければばらないので、
VBAで作ってあったほうが便利だと思っただけです。

ありがとうございました。

投稿日時 - 2013-08-03 12:19:10

ANo.2

非常に細かい指摘で申し訳ないですが、

> sRow = ActiveCell.Row
> sColumn = ActiveCell.Column

ここを
    sRow = Target.Row
    sColumn = Target.Column   ' 使っていないようですが
に変えてみるとどうでしょう?



もう少し細かく言うと・・
・Terget ⇒ 変更されたセル
・ActiveCell ⇒ フォーカスがあるセル
を意味しますから、例えばB2セルの値を打ちかえてEnterキーで確定した時、
通常だとフォーカスは下に移動しますから、
・Target ⇒ B2セル
・ActiveCell ⇒ B3セル
と判断されてしまいますよ。

投稿日時 - 2013-08-02 16:32:09

ANo.1

そもそもがよく分からない。
提供されている、関数をなぜVBAで作成する必要があるのですか?

投稿日時 - 2013-08-02 16:30:54

あなたにオススメの質問