VocExcel(単語帳) / VBA Tips
 
 [Key] エクセル / Excel / VBA /マクロ


<--- 戻る

入力と同時に検索結果を表示する

サンプルファイル







Sub matCimi()


On Error GoTo line

Dim i As Long
Dim q As Long

Dim lstRow As Long

Dim myP As Long

Dim thisSht

thisSht = ActiveSheet.Name




i = 101
q = ListBox1.ListCount
lstRow = Worksheets(mySt).Cells(65536, "H").End(xlUp).Row + 1



For i = 101 To lstRow

i = i - 1

myP = i + Application.WorksheetFunction.Match(Trim(TextBox1.Text), Worksheets(mySt).Range("H" & i & ":H" & lstRow), 0) - 1


Worksheets(thisSht).ListBox1.AddItem mySt 'シート名

Worksheets(thisSht).ListBox1.List(q, 1) = Left(CStr(Trim(Worksheets(mySt).Cells(myP, "K"))), 200) '意味
Worksheets(thisSht).ListBox1.List(q, 2) = Left(CStr(Trim(Worksheets(mySt).Cells(myP, "G"))), 200) 'ID



i = myP + 1
q = q + 1


If q >= 30 Then

Exit For

End If


Next i




Exit Sub


line:


'MsgBox "Error"

End Sub

--