Using .Find to search a spreadsheet and put values in listbox

[ALSav]

I have a 2 column Excel spreadsheet that looks roughly like this:

ProjectID Keyword
ABC1 employment
ABC1 marketing
ABC1 personnel
XYZ1 catering
XYZ1 transport


I also have an excel form with a textbox where a user can type in a projectID. On entering a projectID, I would like to search the spreadsheet for this projectID and load the corresponding keyword(s) into a list box on my form. Here's my code but I always get an error saying 'can't find .Find method of Range object'

Can anyone help??

Dim projectID As String
Dim search
Dim RowNo As Long
Dim keyword As String
Dim c As Range



projectID = TextBox6.Text

'first find the projectID and look in the adjacent cell
'to find the keyword. Add the key word to the lstSelection
'repeat for more keywords for this projectID

With Worksheets("Keywords&quot.Range("A2:A300&quot
Set c = .Find(projectID)
If Not c Is Nothing Then
search = c.Address
RowNo = c.Row
Do
Range("A" & RowNo).Offset(0, 1).Value = keyword
lstSelection.AddItem keyword
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> search
End If
End With

 

[logius]

Well, first off, you're assigning wrong. It should be:

keyword = Range("A" & RowNo).Offset(0,1).Value

Also, your range should be A1:A300 instead of A2:A300

Finally, the line

RowNo = c.Row

should be inside the Do/While Loop. As far as the error you get is concerned, I don't know what it could be. I created a sheet with the data you provided and dumped your code into a module and it ran the first time I tried it. It didn't produce the correct results, but it ran.

What you could try is replacing

Worksheets("Keywords").

with

ActiveSheet

. That was the only change I made when I first ran the module.