Macro to search every row in sheet2 then display all matching resu

[Guest]

My macro button is on sheet1.

On sheet2 is an ever growing database of information. There are 5 different
column values and the information is grouped by row.

(not sure that explains it well)

I want the macro button on sheet1 to initiate a search of the database on
sheet2.

I want it to search all the contents of column A (excluding A1)
For every cell that matches the search I want to select that row and copy
the 5 column values for that row onto sheet1.

Haven't been able to figure this one out.

Any help would be greatly appreciated.

Thank you!
Ryan

 

[Guest]

Assume the item to search for in column A is in Cell B9 of Sheet1 and you
want the found rows to be pasted starting in B10 of Sheet1.

Sub GetData()
Dim sh As Worksheet
Dim sh1 As Worksheet
Dim sAddr As String, s As Variant
Dim rng As Range, rng1 As Range
Set sh1 = Worksheets("Sheet1")
Set sh = Worksheets("Sheet2")
s = sh1.Range("B9")
Set rng = sh.Range(sh.Range("A2"), _
sh.Cells(Rows.Count, "A")).Find(What:=s, _
After:=sh.Cells(Rows.Count, 1), _
LookIn:=xlFormulas, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)

If Not rng Is Nothing Then
sAddr = rng.Address
Do
If rng1 Is Nothing Then
Set rng1 = rng
Else
Set rng1 = Union(rng1, rng)
End If
Set rng = sh.Range(sh.Range("A2"), _
sh.Cells(Rows.Count, 1)).FindNext(rng)
Loop Until rng.Address = sAddr
If Not rng1 Is Nothing Then
Set rng1 = Intersect(rng1.EntireRow, sh.Range("A:E"))
rng1.Copy sh1.Range("B10")
End If
End If
End Sub

 

[Guest]

Thank you Tom!

This worked for searching numerical values!

The problem I am now having is, I need to be able to do the search for Text
and Dates.

When I try entering either of those I get a "Type Mismatch" error. Not sure
where in the code you provided I need to modify it so that it will search
properly.

Thank you again!

 

[Guest]

there should be no problem with a true string value such as a name. Find has
always been tempermental when it comes to dates.

Dates are stored as a number. so perhaps you can convert your date to a
number and have Find look for that. Also, dates can be a floating point
number when they have time values added. Again, 10/25/2008 <> 10/25/2008
8:00AM even if the 8:00 AM isn't displayed. So make sure you are only
looking a Dates with no time (integers). You can try converting your date to
a long. Play with it manually and get it to work. Then turn on the macro
recorder and do the Find again to capture the settings for the arguments.

Lookin:=xlformulas can also be Lookin:=xlValues with formulas, I would
expect it to be looking for the number stored there (date serial number).
With values, it may be looking for the displayed value as a string. So
formatting would be important.

Sorry I don't have a hard and fast set of rules for dates, but I have just
never invested the time to exhaustively test it.

 

[Guest]

Another approach that you might use it to apply an autofilter to your data
and then copy the filtered data.

You might look at Ron de Bruin's site and get some ideas on how to code the
autofilter (and of course the macro recorder can be useful as well)

http://www.rondebruin.nl/copy5.htm

 

[Guest]

Thank you Tom!

I was able to get the text and dates to work. You had it right from the
beginning I just goofed part of the code when adjusting it for my macro.

One final question I'm having trouble with.

I have the following words in the database: Test, Tests, Tester... How can
I adjust it so that doing a search for "Test" brings up all matches with
"test" in it no matter what preceeds or follows it? Like wise with some
cells containing multiple ID numbers, ie (1563, 13446, or 134) <- in one
cell and doing a search for 134 and it finding said cell.?


Thank you very much for all your help Tom!!!!

 

[Guest]

Nevermind. I figured it out.

using::

xlPart instead of xlWhole