search question

Hi !

I desperately would like to ask one of your help. I had never touched VBA, and I have to create a small thing in excel, which i am not able to do since 2 days, and I know it is stupidly simple.

In detail :

- I have a workbook with 2 worksheet. One is where 2 columns of datas exist. The other one where I placed a ListBox, and a Button.
- Worksheet name with datas called : "Data Stored"
- Worksheet where my ListBox and Button is : "Search"
- In "Data Stored" sheet there are hundreds of first names in column A, and hundreds of last names in column B.
- I have one cell in "Search" sheet, where I enter the text I wanna search for. This cell is D11.

What I have to do :

- When I push the button (placed on "Search" sheet), a VBA macro should look into the cell D11, and search all A and B columns in "Data Stored" sheet for the text in D11. If in any of the A or B column cell (in "Data Stored") the text is found even just a part of a cell text, add it to the ListBox (placed on "Search" sheet).
- Thats all.

Could somebody drop me a helping hand in this with a concrete few lines of code PLEASE ?

Many many many thank you for your time and help in advance,

Moore

I assume the controls are from the Control Toolbox Toolbar rather than the
Forms toolbar.

Right click on the sheet tab of Search and select view code.

In the left dropdown select
CommandButton1 (or whatever the name is)
and from the right, select Click

Private Sub CommandButton1_Click()

End sub
should appear in the module

Add code like

Private Sub CommandButton1_Click()
Dim sStr As String, rng As Range, cell As Range
Dim sAddr As String
sStr = Me.Range("D11").Value
With Worksheets("Data Stored")
Set rng = .UsedRange.Columns(1).Resize(, 2).Cells
End With
Set cell = rng.Find(What:=sStr, _
After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell
Do
Me.ListBox1.AddItem cell.Value
Set cell = rng.FindNext(cell)
Loop While cell.Address <> sAddr
End If

End Sub

--
Regards,
Tom Ogilvy


"coolice" <coolice.1vvkmb_1127585120.8629@excelforum-nospam.com> wrote in
message news:coolice.1vvkmb_1127585120.8629@excelforum-nospam.com...
>
> Hi !
>
> I desperately would like to ask one of your help. I had never touched
> VBA, and I have to create a small thing in excel, which i am not able
> to do since 2 days, and I know it is stupidly simple.
>
> In detail :
>
> - I have a workbook with 2 worksheet. One is where 2 columns of datas
> exist. The other one where I placed a ListBox, and a Button.
> - Worksheet name with datas called : "Data Stored"
> - Worksheet where my ListBox and Button is : "Search"
> - In "Data Stored" sheet there are hundreds of first names in column A,
> and hundreds of last names in column B.
> - I have one cell in "Search" sheet, where I enter the text I wanna
> search for. This cell is D11.
>
> What I have to do :
>
> - When I push the button (placed on "Search" sheet), a VBA macro should
> look into the cell D11, and search all A and B columns in "Data Stored"
> sheet for the text in D11. If in any of the A or B column cell (in
> "Data Stored") the text is found even just a part of a cell text, add
> it to the ListBox (placed on "Search" sheet).
> - Thats all.
>
> Could somebody drop me a helping hand in this with a concrete few lines
> of code PLEASE ?
>
> Many many many thank you for your time and help in advance,
>
> Moore
>
>
> --
> coolice
> ------------------------------------------------------------------------
> coolice's Profile:
http://www.excelforum.com/member.php...o&userid=27526
> View this thread: http://www.excelforum.com/showthread...hreadid=470475
>

Hi !

You are amasing !!!!

A VERY VERY VERY BIG THANK YOU !!!!!


The code is working perfectly, except one small issue, that it never stops. It is looping forever.

Is there any idea why it can be ?

It is doinf exactly what need, just never stops, and the Excel keep frozen because of this.

Many thank you in advance,

Moore

I believe there is a typo in the posted version:

Private Sub CommandButton1_Click()
Dim sStr As String, rng As Range, cell As Range
Dim sAddr As String
sStr = Me.Range("D11").Value
With Worksheets("Data Stored")
Set rng = .UsedRange.Columns(1).Resize(, 2).Cells
End With
Set cell = rng.Find(What:=sStr, _
After:=rng(1), _
LookIn:=xlValues, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not cell Is Nothing Then
sAddr = cell.Address '<== correct this line
Do
Me.ListBox1.AddItem cell.Value
Set cell = rng.FindNext(cell)
Loop While cell.Address <> sAddr
End If

End Sub

--
Regards,
Tom Ogilvy


"coolice" <coolice.1vvyib_1127603108.3948@excelforum-nospam.com> wrote in
message news:coolice.1vvyib_1127603108.3948@excelforum-nospam.com...
>
> Hi !
>
> You are amasing !!!!
>
> A VERY VERY VERY BIG THANK YOU !!!!!
>
>
> The code is working perfectly, except one small issue, that it never
> stops. It is looping forever.
>
> Is there any idea why it can be ?
>
> It is doinf exactly what need, just never stops, and the Excel keep
> frozen because of this.
>
> Many thank you in advance,
>
> Moore
>
>
> --
> coolice
> ------------------------------------------------------------------------
> coolice's Profile:
http://www.excelforum.com/member.php...o&userid=27526
> View this thread: http://www.excelforum.com/showthread...hreadid=470475
>

A FATASTIC BIG THANK YOU !!!!!!!!!

You really saved my life. I would like to thank you really really very very much !

Everything is perfect now.

Once more you are great and many many many thank you !!

Moore

why. when i try to run this, do i get an invalid or unqualified reference
for .usedrange?

--


Gary


"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:e24WYFUwFHA.2728@TK2MSFTNGP14.phx.gbl...
>I assume the controls are from the Control Toolbox Toolbar rather than the
> Forms toolbar.
>
> Right click on the sheet tab of Search and select view code.
>
> In the left dropdown select
> CommandButton1 (or whatever the name is)
> and from the right, select Click
>
> Private Sub CommandButton1_Click()
>
> End sub
> should appear in the module
>
> Add code like
>
> Private Sub CommandButton1_Click()
> Dim sStr As String, rng As Range, cell As Range
> Dim sAddr As String
> sStr = Me.Range("D11").Value
> With Worksheets("Data Stored")
> Set rng = .UsedRange.Columns(1).Resize(, 2).Cells
> End With
> Set cell = rng.Find(What:=sStr, _
> After:=rng(1), _
> LookIn:=xlValues, _
> LookAt:=xlPart, _
> SearchOrder:=xlByRows, _
> SearchDirection:=xlNext, _
> MatchCase:=False)
> If Not cell Is Nothing Then
> sAddr = cell
> Do
> Me.ListBox1.AddItem cell.Value
> Set cell = rng.FindNext(cell)
> Loop While cell.Address <> sAddr
> End If
>
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "coolice" <coolice.1vvkmb_1127585120.8629@excelforum-nospam.com> wrote in
> message news:coolice.1vvkmb_1127585120.8629@excelforum-nospam.com...
>>
>> Hi !
>>
>> I desperately would like to ask one of your help. I had never touched
>> VBA, and I have to create a small thing in excel, which i am not able
>> to do since 2 days, and I know it is stupidly simple.
>>
>> In detail :
>>
>> - I have a workbook with 2 worksheet. One is where 2 columns of datas
>> exist. The other one where I placed a ListBox, and a Button.
>> - Worksheet name with datas called : "Data Stored"
>> - Worksheet where my ListBox and Button is : "Search"
>> - In "Data Stored" sheet there are hundreds of first names in column A,
>> and hundreds of last names in column B.
>> - I have one cell in "Search" sheet, where I enter the text I wanna
>> search for. This cell is D11.
>>
>> What I have to do :
>>
>> - When I push the button (placed on "Search" sheet), a VBA macro should
>> look into the cell D11, and search all A and B columns in "Data Stored"
>> sheet for the text in D11. If in any of the A or B column cell (in
>> "Data Stored") the text is found even just a part of a cell text, add
>> it to the ListBox (placed on "Search" sheet).
>> - Thats all.
>>
>> Could somebody drop me a helping hand in this with a concrete few lines
>> of code PLEASE ?
>>
>> Many many many thank you for your time and help in advance,
>>
>> Moore
>>
>>
>> --
>> coolice
>> ------------------------------------------------------------------------
>> coolice's Profile:
> http://www.excelforum.com/member.php...o&userid=27526
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=470475
>>
>
>

Perhaps it didn't copy correctly.

--
Regards,
Tom Ogilvy

"Gary Keramidas" <GKeramidas@msn.com> wrote in message
news:uDWCHNWwFHA.3124@TK2MSFTNGP12.phx.gbl...
> why. when i try to run this, do i get an invalid or unqualified reference
> for .usedrange?
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <twogilvy@msn.com> wrote in message
> news:e24WYFUwFHA.2728@TK2MSFTNGP14.phx.gbl...
> >I assume the controls are from the Control Toolbox Toolbar rather than
the
> > Forms toolbar.
> >
> > Right click on the sheet tab of Search and select view code.
> >
> > In the left dropdown select
> > CommandButton1 (or whatever the name is)
> > and from the right, select Click
> >
> > Private Sub CommandButton1_Click()
> >
> > End sub
> > should appear in the module
> >
> > Add code like
> >
> > Private Sub CommandButton1_Click()
> > Dim sStr As String, rng As Range, cell As Range
> > Dim sAddr As String
> > sStr = Me.Range("D11").Value
> > With Worksheets("Data Stored")
> > Set rng = .UsedRange.Columns(1).Resize(, 2).Cells
> > End With
> > Set cell = rng.Find(What:=sStr, _
> > After:=rng(1), _
> > LookIn:=xlValues, _
> > LookAt:=xlPart, _
> > SearchOrder:=xlByRows, _
> > SearchDirection:=xlNext, _
> > MatchCase:=False)
> > If Not cell Is Nothing Then
> > sAddr = cell
> > Do
> > Me.ListBox1.AddItem cell.Value
> > Set cell = rng.FindNext(cell)
> > Loop While cell.Address <> sAddr
> > End If
> >
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> > "coolice" <coolice.1vvkmb_1127585120.8629@excelforum-nospam.com> wrote
in
> > message news:coolice.1vvkmb_1127585120.8629@excelforum-nospam.com...
> >>
> >> Hi !
> >>
> >> I desperately would like to ask one of your help. I had never touched
> >> VBA, and I have to create a small thing in excel, which i am not able
> >> to do since 2 days, and I know it is stupidly simple.
> >>
> >> In detail :
> >>
> >> - I have a workbook with 2 worksheet. One is where 2 columns of datas
> >> exist. The other one where I placed a ListBox, and a Button.
> >> - Worksheet name with datas called : "Data Stored"
> >> - Worksheet where my ListBox and Button is : "Search"
> >> - In "Data Stored" sheet there are hundreds of first names in column A,
> >> and hundreds of last names in column B.
> >> - I have one cell in "Search" sheet, where I enter the text I wanna
> >> search for. This cell is D11.
> >>
> >> What I have to do :
> >>
> >> - When I push the button (placed on "Search" sheet), a VBA macro should
> >> look into the cell D11, and search all A and B columns in "Data Stored"
> >> sheet for the text in D11. If in any of the A or B column cell (in
> >> "Data Stored") the text is found even just a part of a cell text, add
> >> it to the ListBox (placed on "Search" sheet).
> >> - Thats all.
> >>
> >> Could somebody drop me a helping hand in this with a concrete few lines
> >> of code PLEASE ?
> >>
> >> Many many many thank you for your time and help in advance,
> >>
> >> Moore
> >>
> >>
> >> --
> >> coolice
>
>> ------------------------------------------------------------------------
> >> coolice's Profile:
> > http://www.excelforum.com/member.php...o&userid=27526
> >> View this thread:
> >> http://www.excelforum.com/showthread...hreadid=470475
> >>
> >
> >
>
>