difficult (for me) userform search code

[redspanna]

Hi all,

I have a userform that is to be used so that a song Artist or song title can be input then a search is carried out through a database for any song or artist matching any part of the input string.
results are then shown in sheet on workbook.

Eg

Input into ARTIST box (Texbtbox1) Robbie Williams

a search is made of the data held in sheet1 column C for any matching string or part of string....

Eg

Column C contains the following

Kylie Minogue
Robbie
The Smiths
Robbie Williams

the result(s) to be shown in next available rows of the DATA sheet column D would be....
Robbie
Robbie Williams

hope that makes sense and more so hope you can help..thanks

 

[VoG]

Try

With Sheets("Sheet1")
    LR = Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        With Range("C" & i)
            If .Value Like "*" & UserForm1.textbox1.Text & " *" Then
                Sheets("DATA").Range("D" & Rows.Count).End(xlUp).Offset(1) = .Value
            End If
        End With
    Next i
End With

 

[redspanna]

Hi Vog

thankd for your suggestion however it doesn't see to find anything ....
I have changed the Userform1.textbox1 part of your code to read frminterface.textbox1 (as this is the correct form name) but nothing is found or pasted into D of sheet Data

any ideas as to why?

thanks

 

[VoG]

Perhaps

With Sheets("Sheet1")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        For j = 0 To UserForm.TextBox1.Count
            With Range("C" & i)
                If .Value Like "*" & UserForm1.TextBox1(j).Text & " *" Then
                    Sheets("DATA").Range("D" & Rows.Count).End(xlUp).Offset(1) = .Value
                End If
            End With
    Next i
    Next j
End With

 

[redspanna]

Hi sorry
when used I get a Compile error - Method or data not found and then the code stops on the

.Count

part of the code ?

any more ideas ?

thanks

 

[redspanna]

Hi all,

just ran this code again and error occured "Invalid Next Control Variable ref"
and the code stops at the

Next i

part

Also do I have to change the

For j = 0 To UserForm.TextBox1.Count

part of code to shown my actual Userfrom name or shud I leave it as is?

thanks again

 

[snowblizz]

Peter got the "i" and "j" mixed up. Should be like this.

With Sheets("Sheet1")
    LR = .Range("C" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        For j = 0 To UserForm.TextBox1.Count
            With Range("C" & i)
                If .Value Like "*" & UserForm1.TextBox1(j).Text & " *" Then
                    Sheets("DATA").Range("D" & Rows.Count).End(xlUp).Offset(1) = .Value
                End If
            End With
        Next [COLOR=Red]j[/COLOR]
    Next [COLOR=Red]i[/COLOR]
End With</pre>

 

[redspanna]

Hi,

Ok that I have now adjusted and I now get the error as explained in my previous msg above where the code comes back with Compile error - Method or data not found and then the code stops on the

.count

part of code....as I said earlier should I change the Userform1 part of code to actualy reflect my frm name or leave it as is?

thanks again

 

[VoG]

Thanks snowblizz.

You need to change the names of the sheet, userform and text box to your actual names.