Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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