search question

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