Using Text box for Find / Find Next using VBA

0

I am trying to use a textbox for a Click button to "find Next" whatever is typed in the adjacent search box.  I was able to make the button work but it goes through the whole loop. I cant figure out how to just find one and stop and not look for the next until the find next button is clicked. here is the code

Sub Searchtoollist()

Dim SearchRange As Range
Dim Itemcell As Range
Dim ItemName As String
Dim FirstItemCell As String


ItemName = Range("D11")

Set SearchRange = Range("C15:E15", Range("C14:E14").End(xlDown))

Set Itemcell = SearchRange.find(what:=ItemName, MatchCase:=False, LookAt:=xlPart)

If Itemcell Is Nothing Then
MsgBox "Not found"
Else
FirstItemCell = Itemcell.Address

Do
Itemcell.Select

Set Itemcell = SearchRange.FindNext(Itemcell)
Loop While Itemcell.Address <> FirstItemCell

End If

End Sub

Answer
Discuss

Answers

0

here is your code with a bit of debug msgs 

It works fine so I suspect there is something in the workbook which is not quite right

Sub Searchtoollist()
Dim SearchRange As Range
Dim Itemcell As Range
Dim ItemName As String
Dim FirstItemCell As String
Dim xx
ItemName = Range("D11")
Range("C15:E15", Range("C14:E14").End(xlDown)).Select
MsgBox "Check the range is what you want"
Set SearchRange = Range("C15:E15", Range("C14:E14").End(xlDown))
Set Itemcell = SearchRange.Find(what:=ItemName, MatchCase:=False, LookAt:=xlPart)
If Itemcell Is Nothing Then
MsgBox "Not found"
Else
FirstItemCell = Itemcell.Address
Do
Itemcell.Select
MsgBox "now at " & Itemcell.Address
Set Itemcell = SearchRange.FindNext(Itemcell)
Loop While Itemcell.Address <> FirstItemCell
End If
Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login