PDA

View Full Version : Find all instances of a word and return the results in a userfrom - not working



rrenis
07-25-2007, 06:16 AM
Hi - I have the following code (the find part coming from the VBA help example for find method) but it's not working. Can anyone spot any obvious error's I've made?? :help

Also I think the VBA Find method may be case specific (I know it is is formula's in Excel) - if so is there an alternative method I can use to find all the instances of a word in a column, that would for exampe find instances of the word test that are entered as "test", "Test" or "TEST"? :think:

Private Sub CommandButton1_Click()
Dim SearchText As String
SearchText = "Test"

ListBox1.Clear
ListBox1.Visible = False

With Worksheets("Find Text - Test").Range("D2:D5000")
Set c = .Find(SearchText, LookIn:=xlValues)
If Not c Is Nothing Then
FirstAddress = c.Address
Do
ListBox1.AddItem (c.Value)
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> FirstAddress
End If
End With

ListBox1.Visible = True

End Sub

Thanks for looking.

Cheers,
rrenis

Bob Phillips
07-25-2007, 06:34 AM
Not working, in what way?

It works fine for me, finds Test and test, but the Listbox is a bit boring.

rrenis
07-25-2007, 06:42 AM
Thanks xld - your reply got me looking at things outside the code. Column D is hidden and the sheet is protected - when I unprotected the sheet and had unhidden column D it worked fine!! :doh:
:beerchug:
Cheers,
rrenis