[RESOLVED] search form in excel range only

[Kubull]

Hi guys and or gals,

I'm trying to create a custom seach form for an excel workbook, I got a simple one to work, but i need to get the search within specific ranges, I tried getting the below code to work, but I'm bashing my head against the wall...

Any help would be awesome!

Basically the form has three values

txtName
txtChange
Txtdate
the form should be able to able to search one of the other, none are manditory. I've created the ranges as mentioned in the below code but it keeps falling over...

Private Sub Cmd_OK_Click()
Dim MyText As String
Dim MyMsg As String
Dim MyType
Dim MyTitle As String
Dim rfoundcell As Range
Dim lcount As Long


MyText = txtname.Value
MyText = txtchange.Value
MyText = txtdate.Value
MyMsg = "No matches were found."
MyType = vbOKOnly + vbExclamation
MyTitle = "No Matches"
'
Set rfoundcell = Range("Name")
Set rfoundcell = Range("Change")
Set rfoundcell = Range("Date")

On Error GoTo ErrorHandler

For lcount = 1 To WorksheetFunction.CountIf(Columns(1), txtname)

    Set rfoundcell = Columns(1).Find(What:=txtname, After:=rfoundcell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)

Next lcount

For lcount = 1 To WorksheetFunction.CountIf(Columns(1), txtchange)

    Set rfoundcell = Columns(1).Find(What:=txtchange, After:=rfoundcell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)

Next lcount

For lcount = 1 To WorksheetFunction.CountIf(Columns(1), txtdate)

    Set rfoundcell = Columns(1).Find(What:=txtdate, After:=rfoundcell, _
        LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)

Next lcount

Exit Sub
ErrorHandler:
MsgBox MyMsg, MyType, MyTitle

End Sub

[westconn1]

For lcount = 1 To WorksheetFunction.CountIf(Columns(1), txtname)

Set rfoundcell = Columns(1).Find(What:=txtname, After:=rfoundcell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

Next lcount

this loop will only retain the last found value for rfoundcell, maybe you should be creating a non-contiguous range for each found cell using Union, then you can search that range with the next search parameter

[Kubull]

Hey Westconn1 thanks for your reply, sorry it took me a while to get back to you.

I tried a few new things and i got it to work for searching within ranges only, but now it only finds one result, where as previously it would find more then one, like say "john smith and jane smith" it would only find john. and stop there. can you see where im going wrong?

heres the new code:

Private Sub Cmd_OK_Click()
Dim MyText As String
Dim MyMsg As String
Dim MyType
Dim MyTitle As String

MyText = txtname.Value
MyText = TxtChange.Value
MyMsg = "No matches were found."
MyType = vbOKOnly + vbExclamation
MyTitle = "No Matches"

On Error GoTo ErrorHandler

    Application.Goto Reference:="Name"
        Selection.Find(What:=txtname, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

If txtname.Value = "" Then

    Application.Goto Reference:="CO"
        Selection.Find(What:=TxtChange, After:=ActiveCell, LookIn:=xlFormulas, _
            LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False).Activate

If TxtChange.Value = "" Then

Range("A1").End(xlDown).Select

GoTo finish

Exit Sub
ErrorHandler:
MsgBox MyMsg, MyType, MyTitle
finish:

    MsgBox ("Enter something dummy!")
End If
End If
End Sub

[vbfbryce]

I don't see where you are looping any more. What would make it find more than one result?

[Kubull]

Thats the point, it doesn't. i only managed to get it to search in ranges only, but it only finds one result, usually the first on in the list.

[vbfbryce]

In your original post, you had a For loop.

If you take it out, you'll have to put something else in its place (like a While...FindNext is not nothing, or something like that).

[Kubull]

Got it working excellent many thanks for your help Westconn1 & vbfbryce!!