Find function in VBA

[Garage2k]

Hi everyone im new here so be kind.

Im trying to produce a seach function in VBA that will search a given
column i.e. "H" for a particular word then when it finds this it then
puts the entire row in which it is found into a textbox i.e. A1:H1

I have managed to do this however i encounter problems when a word is
searched for and not found i get

"runtime error 91 Object variable or with block not set"

Here's the code so far albeit fairly primitive.

Private Sub CommandButton1_Click()
ListBox1.Clear
searchcount = 0
description.Hide
Sheets("Sheet1").Select
Columns("H:H").Select
Selection.Find(What:=TextBox1, After:=ActiveCell, LookIn:=xlValues,
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext,
MatchCase:=False).Activate
temp = ActiveCell.Address
countrow = ActiveCell.Row

ListBox1.AddItem Range("A" & countrow).Text & " ¦ " & Range("B" &
countrow).Text & " ¦ " & Range("C" & countrow).Text & " ¦ " &
Range("D" & countrow).Text & " ¦ " & Range("E" & countrow).Text & "
¦ " & Range("F" & countrow).Text & " ¦ " & Range("G" &
countrow).Text & " ¦ " & Range("H" & countrow).Text

Selection.FindNext(After:=ActiveCell).Activate
Do Until ActiveCell.Address = temp

countrow = ActiveCell.Row

ListBox1.AddItem Range("A" & countrow).Text & " ¦ " & Range("B" &
countrow).Text & " ¦ " & Range("C" & countrow).Text & " ¦ " &
Range("D" & countrow).Text & " ¦ " & Range("E" & countrow).Text & "
¦ " & Range("F" & countrow).Text & " ¦ " & Range("G" &
countrow).Text & " ¦ " & Range("H" & countrow).Text

Selection.FindNext(After:=ActiveCell).Activate
searchcount = searchcount + 1
Loop
theend:
description.Show
End Sub

 

[Toppers]

Hi,

Try this which looks for all rows in column H which have value of "textbox1":

Private Sub CommandButton1_Click()
ListBox1.Clear
Description.Hide
With Worksheets("Sheet1").Range("H:H")
Set c = .Find(TextBox1.Value, LookIn:=xlValues)
If Not c Is Nothing Then
firstaddress = c.Address
Do
Countrow = c.Row
ListBox1.AddItem Range("A" & Countrow).Text & " ¦ " &
Range("B" & _
Countrow).Text & " ¦ " & Range("C" & Countrow).Text & " ¦ " & _
Range("D" & Countrow).Text & " ¦ " & Range("E" & Countrow).Text & " ¦
" & Range("F" & Countrow).Text & " ¦ " & Range("G" & _
Countrow).Text & " ¦ " & Range("H" & Countrow).Text

Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
Else
MsgBox TextBox1.Value & " not found"
End If
End With

Description.Show

End Sub

HTH

 

[Mark Hone]

Hi Garage2k,

I've just seen Toppers response, which looks like the right sort of approach.

If you're feeling lazy, a quick and dirty method would be to add in the line:
On Error Goto theend
as the first line of you sub-routine.

This should do the trick but will obviously handle all errors in the same
way. Better to study Toppers method in more detail if you want to learn
something!

Cheers,

Mark

 

[Garage2k]

Thanks for your replies guys

Yes the error code one is the simplist but think i will play aroun
with toppers one as i had best do it properly.

Thanks agai