difficult (for me) userform search code

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
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

Legend
Joined
Jun 19, 2002
Messages
63,650
Perhaps

Code:
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
 
Last edited:

redspanna

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
Hi all,

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

Also do I have to change the
Code:
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

Well-known Member
Joined
Mar 16, 2009
Messages
1,123

ADVERTISEMENT

Peter got the "i" and "j" mixed up. Should be like this.
Code:
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

Well-known Member
Joined
Jul 27, 2005
Messages
1,476
Office Version
  1. 2016
Platform
  1. Windows
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

Code:
.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

Legend
Joined
Jun 19, 2002
Messages
63,650
Thanks snowblizz.

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

Watch MrExcel Video

Forum statistics

Threads
1,122,416
Messages
5,596,010
Members
414,037
Latest member
Roamingsmile

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top