ExcelVBAで行と列の検索

質問

Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ
１　　コード　あ　　い　 う　 え
２　　１０　　○　　　 ○
３　　２０　　　　　○　　○
４　 ３０　　　　　　　　　　○

上記の表が5000件あります。Textbox1に入力し検索ボタンを押すと
A列のコードを検索して一致する列の○のあるところの１行目の項目
をtextbox2に表示したいのですがうまく行きません。
よろしくお願い致します。
Private Sub CommandButton1_Click()
'検索フォームボタン
Dim i As Long
Dim 最終行 As String
Dim サーチ行 As Long
Sheets(1).Activate
最終行 = Range("A1").End(xlDown).Row
サーチ行 = 0
For i = 2 To 最終行
If TextBox1.Value = Range("A" & i) Then
If Range("B" & i, "N" & i) = "" Then
TextBox2.Text = Range("B1", "N1")
サーチ行 = i
Exit For
End If
End If
Next
If サーチ行 = 0 Then
MsgBox TextBox1.Value & "データはありません。", vbInformation, "無し"
End If
TextBox1.SetFocus
End Sub
エラーはでません。データはありませんとなります。

回答No.3

ham_kamo · ベストアンサー

No.2のham_kamoです。

> R1なのですが、Set R1 = .Columns(1).Find(What:=TextBox1.Text, LookAt:=xlWhole)
> これは、１列目をテキストボック1の文字を探して、A列のセル番地を
> かえしてるんですか？

その通りです。Columns(1)はA列全体を表すRangeオブジェクトです。
順番が前後しますが、そのR1のセルを元にして、

Set R2 = .Rows(R1.Row).Find(What:="○", LookAt:=xlWhole)
でR1の行の○を検索し、続けて、

Set R2 = .Rows(R1.Row).FindNext(R2)

でその次の○を検索する、というのをループでまわしています。

> R2.Column > Prev　は、どういう意味ですか？

２つめ以降の○を検索するためにFindNext メソッドを使っていますが、このメソッドは、引数で指定したセルから後を前と同じ条件で検索し、最後までいったら最初に戻るようになっています。

たとえば、A3とE3に○が入っているとして、FindNext を繰り返すと、
A3→E3→A3→E3→A3→E3…
と無限ループに陥ります。そこで、検索して○が見つかったセル（R2）の列番号を
Prev = R2.Column
として Prev に格納しておき、

Set R2 = .Rows(R1.Row).FindNext(R2)

で次の○を検索します。先の例で言うと、ここでE3からA3に戻ったら、ループの先頭に戻ったとき、
Do While R2.Column > Prev
のループ条件が False となり、ループを抜けるようになります。

S = S & IIf(S = "", "", ",") & .Cells(1, R2.Column).Value

の行ですが、これは TextBox2 に表示する文字列 S を組み立てています。
見つかったR2のセルの１行目の値が
.Cells(1,R2.Column)
に入っているので、これを S に追加していきます。

普通に文字列を連結するのであれば、

S = S & .Cells(1, R2.Column).Value

でいいのですが、これでは全部くっついてしまって見にくいので間にカンマを挟むようにしました。そうすると、

S = S & "," & .Cells(1, R2.Column).Value

となるのですが、１番最初は S は空なので、見つかった文字列が「あああ」だとしたら、
,あああ
といきなりカンマで始まる文字列になってしまいます。
そこで "," でなく、
IIf(S = "", "", ",")
としています。IIfはワークシート関数のIFと同じ意味です。要は、Sが""（空文字列）のときは空文字列を、そうでないときはカンマを間に挟む、という意味になります。

お礼

１から１０までありがとうございます。m(_ _"m)ペコリ
S & I ではなくて、IIfなんですね。
はじめて見ました。
FindとFindNextのちがいわかりました。
ありがとうございます。
理屈は理解できても、なかなかできなくて・・・。
丁寧な回答に感謝しております。
今後ともよろしくお願い致します。

回答No.4

kobouzu_su

こんばんは。

こういった場合はham_kamoさんの回答のようにFind等の検索用メソッドを
使うのが一般的ですが、ループの基礎であるFor,Do Loopなどが使えないと。。。。
ということで、その基礎のForを使っての回答を。

'--------------------------------------------------------
Private Sub CommandButton1_Click()
　Dim R As Long
　Dim C As Integer
　Dim LastRow As Long
　Dim LastClm As Integer
　Dim Moji As String

　LastRow = Range("A65536").End(xlUp).Row
　LastClm = Range("IV1").End(xlToLeft).Column

　For R = 2 To LastRow
　　If Cells(R, "A").Value = Val(TextBox1.Value) Then
　　　　For C = 2 To LastClm
　　　　　　If Cells(R, C).Value = "○" Then
　　　　　　　　Moji = Moji & Cells(1, C).Value & ","
　　　　　　End If
　　　　Next C
　　　　Exit For
　　End If
　Next R

　If Moji <> "" Then
　　TextBox2.Value = Left(Moji, Len(Moji) - 1)
　Else
　　MsgBox TextBox1.Value & " は無し", vbCritical + vbOKOnly, "確認"
　　TextBox2.Value = ""
　End If

End Sub
'-------------------------------------------------

お礼

ご指摘のとおり、ループが使えません。
ここで繰り返すんだなぁとぼんやりわりますが
まだまだ、理解できていません。
考えみました。
LastRowでA列の最終行ですね。
LastClmで１行目の最終列ですね。
For R = 2 To LastRow　で、２行目から最後の行まですね。
もしも、A列にtextboxが同じなら、B1から最終列まで見なさいですか？
MojiとCells(1,2)の値に,をつける？のがMOJIですか？
だと、Moji1,Moji2,とかにはなるような・・・。
でも結果はなっていません。

NEXT Rはどういう意味ですか？
Rは最終行までだから、65536行まで繰り返すのでしょうか？

Mojiが空白でないときは、Mojiの先頭から-1した文字列を(,)を
とった文字列をtextbox2に入れなさい。ですか？
難しいです。
しかしながら、ループはやらねばと思っております。
自分でループを作ってみます。
その折にはよろしくお願い致します。
今後もアドバイスよろしくお願い致します。

回答No.2

ham_kamo

ループでもいいのですが、検索するならFindやFindNextメソッドを使うと速いですよ。
私流の書き方で、10と入力して「あ,う」というように表示するようなマクロを書いてみました。

Private Sub CommandButton1_Click()
　Dim R1 As Range, R2 As Range
　Dim Prev As Integer, S As String
　With Worksheets(1)
　　Set R1 = .Columns(1).Find(What:=TextBox1.Text, LookAt:=xlWhole)
　　If R1 Is Nothing Then
　　　MsgBox TextBox1.Text & "データはありません。", vbInformation, "無し"
　　　Exit Sub
　　End If

　　Set R2 = .Rows(R1.Row).Find(What:="○", LookAt:=xlWhole)
　　If R2 Is Nothing Then
　　　MsgBox TextBox1.Text & "データに○がついた項目はありません。", vbInformation, "無し"
　　　Exit Sub
　　End If

　　Do While R2.Column > Prev
　　　S = S & IIf(S = "", "", ",") & .Cells(1, R2.Column).Value
　　　Prev = R2.Column
　　　Set R2 = .Rows(R1.Row).FindNext(R2)
　　Loop
　End With

　TextBox2.Text = S
　TextBox1.SetFocus
End Sub

補足

いつもいつも、お世話になっております。
ありがとうございます。<(_ _)>
自分なりに調べて考えてるうちに返答がおそくなりました。すみません。Columsの使い方がよくわりません。
R1なのですが、Set R1 = .Columns(1).Find(What:=TextBox1.Text, LookAt:=xlWhole)
これは、１列目をテキストボック1の文字を探して、A列のセル番地を
かえしてるんですか？

R2.Column > Prev　は、どういう意味ですか？

S & IIf(S = "", "", ",") & .Cells(1, R2.Column).Value　は、
S&Iはどういう意味なんでしょうか？

○を探しなさいのR2と
Set R2 = .Rows(R1.Row).Find(What:="○", LookAt:=xlWhole)

A列の値を探して、○を探すという意味ですか？
Set R2 = .Rows(R1.Row).FindNext(R2)
よろしければご指導ください。ペコm(_ _;m)三(m;_ _)mペコ

回答No.1

noname#246547

入力されたコードに該当する○印の値を連結してTextBox2に表示したいのですか？

たとえばコード＝１０なら
TextBox2には「あう」とか。※１

もしそうなら、
If Range("B" & i, "N" & i) = "" Then
TextBox2.Text = Range("B1", "N1")
はおかしいでしょう。

これだと、入力コードに該当する行の列Bから列Nまですべて空なら、
1行目のデータをTextBox2に入れることになります。
つまり、○印があったらTextBox2には何も入らない、つまり「データがありません」になるでしょう。

※１の様にしたいなら2重ループになるでしょう。

補足

ありがとうございます。
はい、1の様にしたいです。
すみません、２重ループはどうすればいいのでしょうか？