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!!!!
"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy
"Ryan Hess" wrote:
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!
"Tom Ogilvy" wrote:
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
--
Regards,
Tom Ogilvy
"Ryan Hess" wrote:
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