VBA初心者のための2条件検索方法
- VBA初心者の方に向けて、2条件の検索方法について解説します。具体的な例として、生産番号と日付を条件に、セルに生産数を入力するVBAコードを作成する方法を説明します。
- VBAを使用して、複数のシートにまたがる生産番号と日付の組み合わせを検索する方法について解説します。検索結果は1つだけが返されるため、複数のシートに対して効率的に検索することができます。
- VBAのFindメソッドでは、2条件の検索が難しい場合があります。そのため、生産番号と日付の組み合わせを一致させるためのコンボボックスを使用する方法を紹介します。この方法を用いることで、より簡単に2条件の検索を行うことができます。
- ベストアンサー
VBA 2条件の検索について
VBA初心者です。よろしくお願いします。 下記のような、シートがあります。 A B C D E E ・・・・・ 1 生産番号 型番 ライン 1/18 1/19 1/20・・・・・・ 2 111 XXX 1 3 222 YYY 2 4 333 ZZZ 3 5 444 AAA 5 ユーザーフォームに生産番号と生産数と日付を入力する コンボボックスを作成し、生産番号(列)と日付(行)が一致する セルに生産数を入力するようなVBを作成しています。 (例)生産番号222、生産数200、日付1/20 であれば、 E3のセルに200入力する Findメソッドでは、2条件の検索が当方実力ではできず、 質問させていただく事となりました。 ちなみにシートも複数有り、全てを検索できるようなVBを考えてます。 検索対象の生産番号・日付は1bookで1つしかありませんので、 検索結果が複数はありません。 よろしくご教示お願いいたします。
- nkgw_4a_t
- お礼率32% (9/28)
- その他MS Office製品
- 回答数6
- ありがとう数3
- みんなの回答 (6)
- 専門家の回答
質問者が選んだベストアンサー
#1です。 (2)のコード内で「b」をVariantで宣言していますがDateで宣言してください。 また、最初のErr=91のメッセージボックスの次の行にErr.Clearを追記してください。 あとは Range("X,Y") = 生産数量BOX.Valueではなく Cells(X, Y) = 生産数量BOX.Valueとしてください。 Range("X,Y")ですと「X,Y」という名前の付いたエリアに、となります。 また、仮にRange(X,Y)だったとしても列の指定がアルファベットで無い為動作しません。 On Error Resume Nextを実行している為実際はエラーが起きていますが処理が実行されます。
その他の回答 (5)
- avanzato
- ベストアンサー率54% (52/95)
#1です。 #5の具体的なコードです。 Private Sub UserForm_Initialize() Dim I As Integer Dim X As Integer Dim Y As Integer X = 1 Sheets.Add ActiveSheet.Name = "Temp" For I = 1 To Worksheets.Count If Worksheets(I).Name <> "Temp" Then Y = 2 Do While Worksheets(I).Range("A" & Y).Value <> "" Range("Temp!A" & X).Value = Worksheets(I).Range("A" & Y).Value Range("Temp!B" & X).Formula = "=COUNTIF(A1:A" & X & ",A" & X & ")" X = X + 1 Y = Y + 1 Loop End If Next I Columns("A:A").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _ :=xlPinYin, DataOption1:=xlSortNormal I = 1 Do While Range("A" & I).Value <> "" If Range("B" & I).Value = 1 Then ComboBox1.AddItem Range("A" & I).Value End If I = I + 1 Loop Application.DisplayAlerts = False Sheets("Temp").Delete Application.DisplayAlerts = True End Sub
お礼
遅くなりました。 一度トライしてみます。 色々勉強させて頂きありがとうございました。
- avanzato
- ベストアンサー率54% (52/95)
#1です。 Private Sub Worksheet_SelectionChange(ByVal Target As Range) にどのようなコードを組まれたのかわかりませんがWorksheet_SelectionChangeを使用すると全体的なレスポンスが低下します。 一例ですがフォームを起動したタイミングで全シートの製造番号一覧を生成しコンボボックスに流し込む。 またはテキストボックスの脇に製造番号一覧のリストボックスを置き、リストボックス内製造番号をWクリックすることでテキストボックスに製造番号を流す。 VBAには配列のソートが無い為仮のシートAを挿入し全シートの製造番号をA列に入れて行き、全シートの読み込みが完了したらA列をソートしてB列に重複チェックの数式を入れる。 重複がなく並んだデータをコンボボックスないしリストボックスに入れ、シートAを削除 大まかにはこのような流れになるかと思います。
- avanzato
- ベストアンサー率54% (52/95)
#1です。 返信が遅れましてすみません。 少しコーディングしてみますが再度内容の確認をお願いします。 フォーム ・・・生産番号(コンボボックス) ・・・日付(コンボボックス) ・・・生産数(コンボボックス) ・・・入力ボタン(コマンドボタン) 条件 ・生産番号は同じシート内に複数存在するが他シートには存在しない。 この場合、型番が異なる。 ・日付はブックに対し1つしか存在しない。 ・同じ日付は複数存在しない。 ★ここで疑問になってくることがあります。 条件に従い同シート内に次のようなデータがある場合 ----------------------------- 生産番号 型番 2/1 2/2 2/3 11111111 AAAA 22222222 BBBB 11111111 CCCC ----------------------------- フォームの情報として生産番号・日付・生産数があります。 生産番号が「11111111」の時、生産数を入れる箇所は1行目か3行目か判断する条件はありますか?
補足
ありがとうございます。 貴殿のご質問通り、当方もネックとなり、 下記のように変更して現在作成しているところです。 (1)生産番号のみ入力するユーザーフォーム(生産番号の絞込み)を作成。 生産番号(テキストボックス) 絞込み(コマンドボタン) ・生産番号入力後、絞込みボタンにて 対象番号をオートフィルターにて絞り込む。 (2)(1)終了後、新しいユーザーフォーム(生産数量の入力)を作成 型番(コンボボックス) 生産数(テキストボックス) 日付(テキストボックス) 入力(コマンドボックス) ・絞込んだ生産番号に対する型番を選択し、 生産数、日付を入力後、入力ボタンにて、 対象型番の行番号、日付の列番号を取得後、 対象セルに生産数を入れる。 下記にて自分なりにコードを作成しましたが、(1)まではスムーズに動きましたが、(2)でエラーになってしまいます。 (1) 省略します。 (2) Private Sub UserForm_Initialize() 'フォームが最初に表示された時の初期設定の状態を表します。 型番BOX = "" 生産数量BOX = "" 生産日BOX = Date 型番BOX.SetFocus Dim buf As String If Not ActiveSheet.AutoFilterMode Then Exit Sub With ActiveSheet.AutoFilter.Range If .Columns.Item(1).SpecialCells(xlCellTypeVisible) _ .Count = 1 Then '抽出データがない場合 Me.型番BOX.Clear Else .Resize(.Rows.Count - 1, 1).Offset(1, 1).Copy With New MSForms.DataObject .GetFromClipboard buf = .GetText End With .Application.CutCopyMode = True Me.型番BOX.List = Split(buf, vbCrLf) End If End With End Sub Private Sub 入力_Click() If Len(型番BOX.Value) = 0 Then MsgBox "型番が未選定です" Cancel = True ElseIf Len(生産数量BOX.Value) = 0 Then MsgBox "生産数量が未入力です" Cancel = True ElseIf Len(生産日BOX.Value) = 0 Then MsgBox "生産日が未入力です" Cancel = True Else Dim a As Variant a = 型番BOX.Value Dim b As Variant b = 生産日BOX.Value On Error Resume Next Columns("B:B").Select ActiveSheet.Cells.Find(a, , , xlWhole, xlByRows, xlNext, False).Select X = ActiveCell.Row If Err = 91 Then MsgBox (prompt) & a & "の型番はありません", _ (vbOKOnly + vbExclamation), ("型番検索結果") End If On Error Resume Next Rows("1:1").Select ActiveSheet.Cells.Find(b, , , xlWhole, xlByColumns, xlNext, False).Select Y = ActiveCell.Column If Err = 91 Then MsgBox (prompt) & b & "の日付はありません", _ (vbOKOnly + vbExclamation), ("日付検索結果") End If Range("X,Y") = 生産数量BOX.Value 生産番号の絞込み.Show Unload 生産数量の入力 End If Range("A1").Select End Sub Private Sub 戻る_Click() 生産番号の絞込み.Show Unload 生産数量の入力 Range("A1").Select Selection.AutoFilter Selection.AutoFilter End Sub 長々とごめんなさい。どうも行番号と列番号をうまく取得できません。 ご教示ください。
- avanzato
- ベストアンサー率54% (52/95)
#1ですが確認をしたい点があります。 複数シートとの事ですが生産番号は例えばSheet1に111があるとした時Sheet2に同じく111が存在することはありますか? フォームの生産数はコンボボックスですか?テキストボックスですか?
補足
早速ありがとうございます。 検索対象の生産番号・日付は1bookで1つしかありませんと書きましたが、生産番号はsheet1内に複数ある場合があります。しかし他sheet2,3とかには存在しません。日付はsheet1に存在すると、他シートには存在しません。 生産番号が1sheetに複数存在する場合は、同じ生産番号でも型番が異なります。 フォームは、コンボボックスです。 よろしくお願いします。
- avanzato
- ベストアンサー率54% (52/95)
こんにちは。 Cells.Findで検索をしているのならば全シートをループでCells.Findすます。 この時シート内に対象が無い場合は「ERR」に91が入ります。 この事から先ず生産番号を検索し「ERR」が91でなければそのシートに生産番号が存在することになります。 この時Cells.Find~Activeで生産番号を選択し選択中のセルの行番号を取得します。 次に日付をCells.Find~Activeで列番号を取得。 あとは取得している行番号・列番号に生産数をセットすれば処理が可能です。
補足
早速のご教示ありがとうございます。 もう少し詳細に教えていただけないでしょうか? 当方では、文章だけでは、理解できないレベルですので。。 すいませんがお願いします。
関連するQ&A
- Excel VBA 条件検索について
ExcelVBAで 「データ検索後リスト表示をして、そのリストから該当するシートを選べば表示される」 というユーザーフォームを作成したいのですが、やり方がよく解りません。どなたか教えて下さい。 具体的には 「コマンドボタンが押された時に、ユーザーフォーム内のテキストボックスに入力された値(名称、日付等)と、複数のシート内のセルの値(名称、日付等)を比較して、一致(全一致、一部一致)した場合、ユーザーフォーム内のリストボックスに表示させる」 というものと 「リストボックスに表示されたものの中から見たいシートを選択すると、そのシートを表示する」 というものです。 リストの表示形式は シート名 名称 日付等 Sheet1 りんご 2013.01.01 Sheet2 りんご 2013.01.02 という具合にしたいと思っています。 ちなみに複数のシートと言いましたが、マスターシートを作りコピーして使用しますので、同一形式のものになります。 以上になります。 色々やってみましたが、うまくいきませんので、どなたか解る方は教えて下さい。 よろしくお願いします。
- 締切済み
- Visual Basic
- 検索値と検索先のシートが別でもOKなVBAはありますか?
「Excel2002」を使用して、内線番号表を 作ろうと思っています。 「表紙」sheetにあるコンボボックスで選んだ 値(各部署名)が、 「内線番号表」sheetの「E1」に入るとします。 (「LinkedCell:内線番号表!E1」) その「E1」を検索値として「内線番号表」内を 探すマクロを、フォームボタンに登録しました (以下。自分で書いたわけではないのですが)。 Sub 部署を表示() With Worksheets("内線番号表") Sheets("内線番号表").Select Cells.Find(What:=Range("E1"), After:=Cells(2, 1), LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, MatchByte:=False, SearchFormat:=True).Activate End With End Sub これはこれでうまくいったのですが、少し問題が 出てきてしまいました。 「内線番号表」sheetはデータを取り込んで、 列や行を大幅にいじる事が頻繁にあるので、 コンボボックスの「LinkedCell」として指定した 「内線番号表!E1」という アドレスが勝手に変更されてしまうのです。 (「$」をつけても駄目でした) そこで、コンボボックスの「LinkedCell」を、 いじる事がない「表紙」sheetの セルに変えたいと思うのですが… 「検索先のシートの中に検索値がなくても 検索出来る」方法はあるのでしょうか? つまり、「表紙」sheetのセルの値を元に、 「内線番号表」sheetを検索するVBAのコードが あれば、教えて頂きたいのですが…。 (これが出来れば、他にも応用したいと思うので) コントロールBOXやVBAは勉強し始めた ばかりなので、うまく説明出来たかどうか 分かりませんが…よろしくお願い致します。
- ベストアンサー
- オフィス系ソフト
- エクセルの関数でIPアドレスのあるブロック部分だけ文字列を取り出したい
A1セルに www.xxx.yyy.zzzというIPアドレスがあります。 このうちyyyの部分だけ、関数を使用してB1セルに自動で入力するようにしたいです。 www.xxx.の文字数は常に固定ですが、yyy.zzzは文字数が変動します。 yyy、zzzの各ブロックは最低1文字、多くて3文字になります。 お手数ですが、ご教授頂きたくお願い申し上げます。
- ベストアンサー
- オフィス系ソフト
- VLOOKUPの検索値について
エクセルでVLOOKUPの関数を使うときの「検索値」に関する質問です。 この「検索値」が数式結果だと反映されませんよね? そこで範囲指定をして、「値だけコピー貼付」をしたのですが、 やっぱり反映されません。 <sheet1> A B C D 1 123 りんご 10円 XXX 2 124 みかん 20円 YYY 3 125 めろん 30円 ZZZ 4 126 かき 40円 QQQ ↑ この「123」や「124」が 別のシートからVLOOKUPで「DのセルがXXXなら123にする」 という数式の結果なんです。 この<sheet1>を利用して、別の<sheet2>に VLOOKUPで「“123”と入力したら“りんご”となる」 というようにしたいのですが、 この“123”“124”が数式結果のため、反映されません。 値だけ貼り付けても反映されません。 上書きで“123”“124”と入力すれば反映されますが 数が多いので困っています。 初心者なので、できるだけわかりやすく教えていただけたら 嬉しいです。
- ベストアンサー
- オフィス系ソフト
- VBAを使ってセルを検索後別シートのデータを自動入力したいです。
見ていただきありがとうございます。 エクセルの2000VBAを使って次のようなことを考えています。 シート2に以下のようなデータがあります。 2006/7/20 コード 数量 100 200 200 400 データの数は日によって違います。 シート1には以下の表があります。横軸にはコード縦軸には日付が入っており各対応するセルに数量が入っています。 100 200 300 400 ....←コード 2006/7/1 20 40 100 800 2006/7/2 50 60 200 500 . . . 2007/6/30 このような場合、シート2にコマンドボタンを設けて押したときシート2の日付とコード番号によりシート1の表の検索を行って対応するセルにシート2のデータを転記したいのです。シート2のデータは本日分のデータが入った時点でシート1への転記を行います。(1日一回です) また、シート1の表は2007/6/30(これ以降は必要ないので)までの日付がすでに入力されており明日以降のデータの入るセルは空白になっております。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
- ExcelのVBAにてキーになるものを入力すると、その関連データを振り分ける
2つのシートがあり、sheet2に振分けるデータ(下表)を作成しておきます。 (列) 1 2 3 (行) 1 A B C 2 ○ △ × 3 ア イ ウ (1)sheet1にコンボボックスにてAを入力する。AはセルA1(固定)に、BはセルC3(固定)に、Cは、セルE5にそれぞれ決められたセルにコピーされる。 (2)sheet1にコンボボックスにて○を入力する。○はセルA1(固定)に、△は、セルC3(固定)に、×は、セル(E5)にそれぞれ決められたセルにコピーされる。 (1)と(2)のコピーされる場所は、同じ場所です。 というようなものをExcelのVBAにて作成したいのですが、VBAは、あまりよくわかりません。 宜しくお願い致します。
- ベストアンサー
- オフィス系ソフト
- AccessVBAで複数条件で検索する方法
お世話になります。 複数条件での検索をしたいのですが、分類をコンボボックスから選び、日付をテキストボックスに 2002/08/01 ~ 2002/08/14 と入力してボタンを押すとサブフォームにその結果を出すようにしたいです。 可能でしょうか?? 宜しくお願いします!!
- 締切済み
- Visual Basic
- VBAマクロ・・同一セルの検索と検索したセルの操作
VBAマクロ・・同一セルの検索と検索したセルの操作 VBAのほぼ初心者です。 決められたセル(たとえばE100)に入力された文字列と同一の文字列を その列内の上(E1~E99)で検索する。 そして、検索できれば(たとえばE30)そのセルがある行(行30)を削除する。 なお、複数検索されれば、検索されたすべてのセルに対応する行を削除する。 というようなことをVBAのマクロで作成したいです。 FindメソッドとDo..Loop Whileを使って試行錯誤してみたのですが、うまくいきません。。 いい方法があれば、是非教えていただきたいです。 どうぞよろしくお願いいたします。
- ベストアンサー
- Visual Basic
- エクセルでの検索について
初めて質問させていただきます。 エクセルシートに1万個程度の番号が 縦(行)に並んでいます。なお、番号はそれぞれ異なります。 あるフォームに日付と10個の異なる番号を入力すると、 エクセルシートから合致する番号を検索し、 エクセルシートの一行目に日付が自動的に入力され、 その行を色塗りしたいと考えております。 これをVBA(マクロ)にて作成したいのですが、 初心者なのでどのようにやればよい分かりません。 どうか教えていただけないでしょうか。 よろしくお願いします。
- 締切済み
- オフィス系ソフト
- 【エクセルVBA】条件に一致した項目の数を別シートへ表示
はじめまして。VBA初心者です。 sheet1のA列に日付(一ヶ月分)、B列に製品名 sheet2のB2セル~AF1セルに日付、A2セル~A10セルに製品名 が入っています。 Sheet1の製品名の数を日付ごとにカウントして、その結果をSheet2の 各日付へ入力するVBAを作成したいのですが、うまくいきません。 分かり難い質問で、大変申し訳ありませんが、どなたか教えて下さい。 よろしくお願いします。
- ベストアンサー
- オフィス系ソフト
お礼
ずばりです。 自分が思い描いていた様に動作できました。 ありがとうございました。 あともう1点教えて頂きたいのですが、 現在、生産番号をテキストボックスに直接入力していますが、 Columns("A:A")のセルを選択すると、 自動的にボックスに入るようにできないでしょうか? Private Sub Worksheet_SelectionChange(ByVal Target As Range)にて、 コードを組んでもうまく作動してくれません。 何度もすみませんが、ご教示お願いいたします。