How do i return muliple lines of data.

[Natalie]

Hi,

I am totally stuck. I have an excel spreadsheet, and I want to do the
following

On Sheet2 field A2 is 23/11/2009.

I have a spreadsheet with lots of information in, and i want to find all
items that the date is 23/11/2009 and populate a new table. I want to display
actual data.
I am trying to set this up to run from a macro that has a pop up box to
enter the date. I can get the macro to work with a pop up box and I can get
it to pull one line with a vlookup, but I don't know how to pull mulitple
data.

Please help me!

 

[Gary''s Student]

Severl things at issue:

1. VLOOKUP() is a great way to grab data, but it only gets one value. To
get more than one value, see:

http://office.microsoft.com/en-us/excel/HA012260381033.aspx


2. The usual method is to use AutoFilter:
a. set the filter
b. set the criteria ( hides or reveals rows)
c. copy the visible material
d. paste elsewhere

3. Because the AutoFilter approach can be implemented either manually or
programmatically, it can be integrated with your efforts. It is very fast,
even with large amounds of data.

4. Another approach is "brute farce" Just grind down the column in a loop
and gather the data as you find it.

 

[Natalie]

This would be fine, but none of your suggestions will work within a macro. I
think I have sorted it now. I used the macro to insert a new column, then if
the data matched the date, then put YES in. Then I can use autofilter! Seems
to work at the moment!

 

[Jim Thomlinson]

try this code... It pastes to Sheet 2 but you can put the values where ever
makes your heart go pitter patter...

Sub FindStuff()
Dim rngTosearch As Range
Dim rngFound As Range
Dim rngFoundall As Range
Dim strWhat As String
Dim strFirst As String

strWhat = InputBox("Enter a date")
Set rngTosearch = Columns("A")
Set rngFound = rngTosearch.Find(What:=DateValue(strWhat), _
LookIn:=xlFormulas)
If Not rngFound Is Nothing Then
Set rngFoundall = rngFound
strFirst = rngFound.Address
Do
Set rngFoundall = Union(rngFound, rngFoundall)
Set rngFound = rngTosearch.FindNext(rngFound)
Loop Until rngFound.Address = strFirst
rngFoundall.EntireRow.Copy Sheet2.Range("A1") 'Change me
End If
End Sub