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

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" wrote in
message ...

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


--
coolice
------------------------------------------------------------------------
coolice's Profile: http://www.excelforum.com/member.php...o&userid=27526
View this thread: http://www.excelforum.com/showthread...hreadid=470475

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

--


Gary


"Tom Ogilvy" wrote in message
...
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" wrote in
message ...

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

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" wrote in
message ...

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

Perhaps it didn't copy correctly.

--
Regards,
Tom Ogilvy

"Gary Keramidas" wrote in message
...
why. when i try to run this, do i get an invalid or unqualified reference
for .usedrange?

--


Gary


"Tom Ogilvy" wrote in message
...
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" wrote
in
message ...

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

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


--
coolice
------------------------------------------------------------------------
coolice's Profile: http://www.excelforum.com/member.php...o&userid=27526
View this thread: http://www.excelforum.com/showthread...hreadid=470475