• ベストアンサー
  • 困ってます

エクセル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

共感・応援の気持ちを伝えよう!

  • 回答数5
  • 閲覧数785
  • ありがとう数3

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

  • ベストアンサー
  • 回答No.4
  • keithin
  • ベストアンサー率66% (5277/7938)

マクロを使おうとしているのですから, この部分: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

共感・感謝の気持ちを伝えよう!

質問者からのお礼

参考にさせていただき、無事できました。 ありがとうございます。 Aの部分は無視していただいて正解です。 「B列に商品名が記入されたらA列にコードを記入させる」 が知りたかったことです。 みなさんに結局言われていますが、 VBAで作成する意味が無いと言われると思い、 「他の部分はVBAで作ってます」とアピールしただけです。 必要なかったですね。 詳しい説明、ありがとうございました。

関連するQ&A

  • VBAでVLOOKUP関数を使う

    「在庫検索」に下記条件を追加するには、どうすれば良いのでしょうか。 1)G列が1500より大きければ Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 2, False) 2)G列が1500より小さければ Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 3, False) どちらの際も空白の条件、If ActiveCell.offset(i, 1).value = ""は残ります。 1)、2)とandを組み合わせる方法でチャレンジしたのですが、出来ませんでした。 ------------------------------------------------------------------------ 以下がベースの「在庫検索」です。 一度、質問して解決したのですが、更なる問題が発生してしまいました。 ご指導ください。 ------------------------------------------------------------------------ Sub 在庫数検索() Dim SerchName As String Dim SerchArea As Range Dim Results As Variant '初期設定 Range("A2").Activate ItemCode = Range("A2").Value i = 0 '検索範囲の設定(ポイント1) Set SerchArea =Worksheets("シート2").Range("List1") '商品コードが空になったら終わり Do Until ItemCode = "" If ActiveCell.offset(i, 1).value = "" Then '★1 On Error Resume Next ItemCode = ActiveCell.offset(i, 0).value Results = Application.WorksheetFunction.VLookup(ItemCode, SerchArea, 2, False) If Err <> 0 Then Results = "" ActiveCell.offset(i, 1) = Results End If '★1 i = i + 1 Loop

  • エクセルVBA(掛け算)

    いつもおせわになります。 現在、下記のようなコードを書いてますがどうもうまくいきません。よろしくお願いいたします。 M列 = K列 × N列を6行目から最終行目で入れたくて下記のようなコードを書きました。 ところが・・・N列にはデータのない場合があるので、If~を入れてみました。ここまではうまくいったのですが、 O列 = K列 × P列のように数式を入れたい列が他にもあり、又同じコードを下記のように書いたら、P列にデータがないところで止まってしまいます。 '///////////////////////////////////////////// Dim wsS As Worksheet Dim r As Long Dim Srow As Long Set wsS = Worksheets("syukei") Srow = wsS.Range("D65536").End(xlUp).Row With Worksheets("syukei") For r = 6 To Srow If Cells(r, 12) = Noting Then r = r End If Cells(r, 13) = Cells(r, 11) * Cells(r, 12) Next End With With Worksheets("syukei") '↓////////ここらへんで止まる////////// For r = 6 To Srow If Cells(r, 14) = Noting Then r = r End If Cells(r, 15) = Cells(r, 11) * Cells(r, 14) Next End With End Sub 掛け算を入れたい行は、下記のようになっています。 M列=K列×L列 O列=K列×N列 Q列=K列×P列 S列=K列×R列 U列=K列×T列 W列=K列×V列 Y列=K列×X列 よろしくお願いいたします。

  • VBA 実行時エラー1004(その2)

    毎度お世話になっております。 シート「sheet2」のA列のリスト内容を、シート「M_得意先」のリストからVLOOKUPして、指定のセルに書き出していくというコードを作成してみたのですが、VLOOKUPを実行する段階でエラーが出てしまいます。 少し変更して、同一シート内でのVLOOKUPは問題なく実行できたのですが...原因をご存知の方教えてください。 Dim b As String Dim endRcell2 As Long Dim cnt10 As long Sheets("sheet2").Select Sheets("sheet2").Range("A1").CurrentRegion.Select 'データ全体選択 Selection.SpecialCells(xlCellTypeLastCell).Select '最終行検出 endRcell2 = ActiveCell.Row cnt10 = 2 Do ↓実行時エラー1004が出る行 b = Application.WorksheetFunction.VLookup(Sheets("Sheet2").Range("A" & cnt10).Value, Sheets("M_得意先").Range(Cells(1, 1), Cells(endRcell, 2)), 2, False) ↑実行時エラー1004が出る行 Sheets("sheet2").Range("E" & cnt10).Value = b cnt10 = cnt10 + 1 Loop Until cnt10 = endRcell2

その他の回答 (4)

  • 回答No.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 さんと同じく、商品コードと商品名の記入の順序が逆な気がして、違和感がバリバリではありますが…。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

始めて2週間ほどのド素人のため、説明不足をお許しください。 無難な「商品」という言葉に置き換えましたが、実際は大きな「商品」の中にA,B,Cなどと細かく分かれており、 それぞれの顧客に対しての自分の仕事を管理したくて作っています。 「商品コード」は、会社の管理しているデータを確認するためには、コードが必要なため、 後付でいいので表示させたいと思いました。 その部分の方法を知りたかったというわけです。 関数でも十分事足りていますが、セル1つに対しての式なので、 数が増えるとコピーしていかなければばらないので、 VBAで作ってあったほうが便利だと思っただけです。 そこまでの説明が必要だと思わなくて、省いてしまいました。 勉強になりました。 ありがとうございます。

  • 回答No.3

あー、もう一つ。 #1さんのご指摘ももっともなのですが・・ そもそも、通常のデータベース的思考でいくと、 「商品コード」を見て「商品名」を返す、だと思いますよ。 #1さんのご指摘も、この指摘も、 「そうしたい理由」というのがどこかにあるのかもしれませんので 安易な否定は避けておきますが・・ まずは「本当にそうしなければいけないのか?」から考えると良いかもしれません。 私も基本的には「この処理は不要」と思いますが、 Targetの使い方は今後のために覚えておいて損は無いですよ。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

Targetの使い方、勉強になりました。 無難な「商品名」という言葉に置き換えましたが、実際は大きな『商品』の中にA,B,Cなどと細かく分かれており、 それぞれの顧客に対しての自分の仕事を管理したくて作っています。 「商品コード」は、会社の管理しているデータを確認するためには、コードが必要なため、 後付でいいので表示させたいと思いました。 だから「商品名」から「商品コード」を返したかったのです。 関数でも十分事足りていますが、セル1つに対しての式なので、 数が増えるとコピーしていかなければばらないので、 VBAで作ってあったほうが便利だと思っただけです。 ありがとうございました。

  • 回答No.2

非常に細かい指摘で申し訳ないですが、 > sRow = ActiveCell.Row > sColumn = ActiveCell.Column ここを     sRow = Target.Row     sColumn = Target.Column   ' 使っていないようですが に変えてみるとどうでしょう? もう少し細かく言うと・・ ・Terget ⇒ 変更されたセル ・ActiveCell ⇒ フォーカスがあるセル を意味しますから、例えばB2セルの値を打ちかえてEnterキーで確定した時、 通常だとフォーカスは下に移動しますから、 ・Target ⇒ B2セル ・ActiveCell ⇒ B3セル と判断されてしまいますよ。

共感・感謝の気持ちを伝えよう!

  • 回答No.1
  • uen_sap
  • ベストアンサー率16% (67/407)

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

共感・感謝の気持ちを伝えよう!

関連するQ&A

  • Vlookupマクロにつきまして

    初心者で、 エクセルのマクロにて、下記の処理を行おうとしているのですが、 上手く行きません。 どなたかお助けください! B3=Vlookup(A3,Sheet2!A:F,3,False) B4 A4 B5 A5 とA列に値がある限りカウントアップしていきます。 以下の通り考えてみたのですが、動きません。 Sub vlookup() Dim i As Long For i = 3 To Cells(Rows.Count, "A").End(xlUp).Row Cells(i, "B") = Application.WorksheetFunction.VLookup(Range(Cells(i, "A")), Worksheets("Sheet2").Range("A:F"), 3, False) Next End Sub すみません、宜しくお願いします。

  • VBA Vlookup #N/A表示させない方法

    こんばんわ。 VBAでVLOOKUPを実施していますが値が見つからない場合#N/Aが表示されてしまいます。 これを表示させない良い方法ありますでしょうか? 以下のようにCells(1,i)がブランクになるまで別シート(日別商品別集計)の40列目の値を検索するロジックです。処理はうまくいっていますが値がない場合は” ”にしたいのですが”#N/A”が表示されてしまいます。 Do While Cells(1, j) <> ""  ActiveCell.Value = Application.VLookup(Cells(1, j), Worksheets("日別商品別集  計").Range("A2:AN533"), 40, False) *エラーの場合 On Error GoTo ERR_1 ERR_1: ActiveCell.Value = "" j = j + 1 ActiveCell.Offset(0, 1).Select Loop どなたかご教授いただけませんでしょうか? よろしくお願い致します。

  • Excel2013VBAでVLOOKUP条件付き

    いつもお世話になっております。 Excel2013VBAでVLOOKUP関数を使用して、商品名に対応した商品コードをとりたいのですがうまくいきません。 アクティブシートの表のA列、D列、G列に商品名を複数入力してあります。 テーブルシートのB列に同じく商品名があり、C列に商品コードがあります。 VLOOKUPを使ってテーブルシートから商品コードを取り出して、アクティブシートのA列、D列、G列の商品名の下の行に商品コードを入力するにはどうすればよろしいでしょうか? 条件として、テーブルシートのC列の商品コードは空白のものが存在する。その場合は処理する必要なし。 アクティブシート内の商品名の下の行が空白の場合のみ処理を行う。商品名が連続して入力してある箇所で商品コードが存在する場合は、メッセージでセル番地を表示して、処理を中断。ただし連続して入力してあっても、商品コードが存在しないものは問題なしとして次の処理を行う。 一部作りかけのものを提示します。また、下記コードはブレークポイントで停止しながらだと一応動作していましたが、普通に動かすとフリーズを起こします。すいません。 たびたびすいませんが、よろしくお願いいたします。 Sub VLOOKUP検索() Dim h As Range, ac As Range Application.ScreenUpdating = False Set ac = ActiveWindow.ActiveCell On Error Resume Next For Each h In Range("A:A,D:D,G:G") If h.Offset(1, 0) = "" Then h.Offset(1, 0) = Application.WorksheetFunction.VLookup(h, Worksheets("テーブル").Range("B:C"), 2, False) End If Next Application.ScreenUpdating = True ac.Select End Sub

  • エクセルVBAマクロの質問です。

    マクロ初心者です。 データ処理のマクロを作ろうとしていて、ちょっと困ってます。 (sheet1) 11 国総 1A (空きセル ) 12 化基 2I (空きセル) ・ 以下、200程度のデータ C列のデータの種類は10種類 (sheet2) 1A 2 101 102 2I 3 103 104 105 ・ ・ sheet1のC列と同じデータ C列より右側のデータ数は1から4個程度 (sheet3) データなし sheet1のデータを、sheet3にコピーする際に、各データのD列に、sheet2のC列の右側のデータを入れていきたいんです。具体的には (sheet3) 11 国総 1A 101 11 国総 1A 102 12 化基 2I 103 12 化基 2I 104 12 化基 2I 105 っていう感じです。先日、こちらのカテゴリでなく、間違えてVBAプログラムの方に質問して、「板違いですよ」と諭されながらも、ご協力いただきsheet2の件数分増やしてコピーするコードまではたどり着いたのですが、その後、どうすればD列に移せるのかで悩んでます。ちなみに、いまたどりついたコード文は以下の通りです。 一度、このコード文の続きで、sheet3のC列を条件カウントするコードを作ってみたんですが、動いてくれませんでした。 Sub Re8928577a() Dim M4 As Range Dim P As Variant ' WorksheetFunction.VLookup Dim Kensaku As String Dim L As Long Dim PRow As Long Dim i As Long Dim Z As Long   Set M4 = Sheets("Sheet2").Range("A1:B30")   L = Sheets("Sheet1").Cells(Rows.Count, "A").End(xlUp).Row '   For Z = 1 To L - 1     Kensaku = Sheets("Sheet1").Cells(Z + 1, 3).Value     P = WorksheetFunction.VLookup(Kensaku, M4, 2, False)     PRow = Sheets("Sheet3").Cells(Rows.Count, "A").End(xlUp).Row       For i = PRow + 1 To PRow + P         Sheets("Sheet1").Rows(Z + 1).Copy Sheets("Sheet3").Rows(PRow + 1).Resize(P) Next i   Next Z End Sub

  • エクセルVBAマクロの質問です。

    マクロ初心者です。行き詰まってます。 sheet1には300件程度のデータがあります。 このデータの3列目の値を、VLOOKUPでsheet3のA1:B30範囲から参照します。そこで取得した回数分、sheet1の各行のデータをsheet2にコピーしたいんです。 そこで、コード文を作ってみましたが、マクロがうまく動きません。 すみませんが、お知恵を貸していただけないでしょうか? Dim Z as Long Dim L As Long Dim P As Long Dim Kensaku As String Dim M4 As Range Dim PRow As Long Dim i As Long Set M4 =Sheets(“sheet3”).Range(“A1:B30“) L = Sheets(“sheet1”).Range(“A1”).End(xlup).Row For Z = 1 to L-1 Kensaku = Sheets(“sheet1”).Cells(Z+1,3).Value P=Worksheetfunction.Vlookup(Kensaku,M4,2,False)    For i = 1 to P      Prow=Sheets(“sheet2”).Range("A1").End(xlDown).Row      Sheets(“sheet1”).Rows(Z+1).Copy Sheets(“sheet2”).Rows(Prow)    Nexti Next Z

  • VBAでVlookupを使って

    いつもお世話になっています。 VBAで現場検索登録シートと言うところに 一覧シートからVlookupを使って 表示させようと思い作りました。 エラーも何もでません。 ただ、表示されるものがちゃんと検索されるものと まったくされないものがあります。 Vlookupはマクロの記録から作ったものです。 原因がわかりません。 よろしくお願い致します。 '検索ボタン Private Sub CommandButton6_Click() '送り方 Range("C4:D4").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,22,FALSE)" Selection.Value = Selection.Value '封筒 Range("C5:D5").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,23,FALSE)" Selection.Value = Selection.Value '得意先名ふりがな Range("C6:F6").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,6,FALSE)" Selection.Value = Selection.Value '得意先名 Range("C7:F8").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,4,FALSE)" Selection.Value = Selection.Value '現場名ふりがな Range("C9:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,9,FALSE)" Selection.Value = Selection.Value '現場名 Range("C10:H11").Select ActiveCell.FormulaR1C1 = "=VLOOKUP(R2C5,一覧!R5C1:R2000C54,7,FALSE)" Selection.Value = Selection.Value End Sub エラーも何もなく、ちゃんと動くことは動きます。 検索結果が、検索コードによって違うのです。 よろしくお願い致します

  • ExcelマクロでVLOOKを実行したい

    ExcelマクロでVLOOKを実行したい 同一シートにある「全体」の表から必要な項目をVLookで抜き出したくて 下記のマクロを作成しました。 「Sheet1」のA列(A2以下)には検索値(数字6ケタ)を入れています。 A2の検索値でヒットした値はB2・C2に入りましたが、A3以下の検索値は スルーされてしまいます。どこを直したら良いのか、ご教授ください。 よろしくお願いします。 ----------------------------------------------------------------------- Sub 検索して値を取得する() Dim 範囲 As Range Dim 検索値, i As Long Dim 出荷日 As Date Dim 商品名 As String Set 範囲 = Worksheets("全体").Range("E7:HG1000") Set 検索値 = Worksheets("Sheet1").Cells(i + 2, 1) If 検索値.Value <> "" Then 商品名 = Application.WorksheetFunction.VLookup(検索値, 範囲, 2, False) 出荷日 = Application.WorksheetFunction.VLookup(検索値, 範囲, 160, False) Cells(i + 2, 2).Value = 商品名 Cells(i + 2, 3).Value = 出荷日 i = i + 1 End If End Sub

  • Excel VBA VLOOKUP の書き方

    次の関数をVBAで書くにはどのように記述すればよいでしょうか? =IF(ISNA(VLOOKUP(B2,Sheet2!A2:B9,2,FALSE)),"",VLOOKUP(B2,Sheet2!A2:B9,2FALSE)) よろしくお願いします。

  • VLookupで一致しなかった時のVBAでの処理

    On Error &#65374;を使わないで、 VLookup()で一致しなかった時の処理をさせたいのですが どのように記述すればよいでしょうか。 例えば、以下のようなコードの場合、 一致したデータがない時にyに-1を代入するには 以下のコードをどのように記述すればよいのでしょうか。 --------------------- Dim x As Integer Dim y As String x = 7 y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) --------------------- 以下はいずれもエラーになりますが、以下のような感じで処理がしたいです。 --------------------- If IsError(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False)) Then  y = -1 Else  y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) End If --------------------- If Application.WorksheetFunction.IsNA(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False)) Then  y = -1 Else  y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) End If --------------------- y = Application.WorksheetFunction.IfError(Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False), -1) --------------------- なお、以下のように本来エラーではない処理で On Error Resume Nextを使うのは、 本当のエラーの処理と混同するため不可 --------------------- On Error Resume Next y = Application.WorksheetFunction.VLookup(x, Worksheets("Sheet1").Range("A1:B100"), 2, False) If Err <> 0 Then y = -1 On Error GoTo 0 ---------------------

  • VBA VLOOKUP 別のファイルを参照

    VBA VLOOKUP 別のファイルを参照 いつもこちらでお世話になっている者です。 VBAの勉強をしております。 別のファイルからVLOOKUPで値を参照したいのですが、 範囲を指定してみましたが、 「worksheetFunctionクラスのVlookupプロパティを参照できません」 とのメッセージが出てしまいます。 なお、値は空白になる行もありますので、 if関数で回避してみましたがうまくいきません。 いろいろ試しましたが、何度やってもうまくいかないので こちらに質問させていただきました。 お詳しい方、ご伝授いただければ助かります。 よろしくお願い致します。 環境はExcel2007です。 Sub sample() Dim 範囲 As Range Dim wb As Workbook, wb2 As Workbook Dim r As Integer,intRow As Integer Workbooks.Open Filename:="***.xlsm" Set wb = ThisWorkbook Set wb2 = ActiveWorkbook Set 範囲 = wb2.Sheets("PvtSht2").Range("Database3") r = wb.Sheets("sheet1").Range("A28:N28").End(xlToRight).ColumnintRow = 3 With wb.Sheets("sheet1") Do Until .Cells(intRow, 1).Value = "" .Cells(intRow, (r + 1)) = Application.WorksheetFunction.If((Application.WorksheetFunction.VLookup(Cells(intRow, 1), 範囲, 2, False)) = 0, "", Application.WorksheetFunction.VLookup(Cells(intRow, 1), 範囲, 2, False)) intRow = intRow + 1 Loop End With End sub