# Solved: Excel search Macro needed



## budward (Oct 28, 2006)

I am looking for a macro that will find every every
instance of the word SATIN (or any other word in Col B)and return it with the associated 
item key # with search results starting in Row E12.
When I do a new search I would need all the old search results cleared out.
There are 3000 rows in the actual list.


----------



## bomb #21 (Jul 1, 2005)

Hi bud.

Clear the text in E7:E11 first. Then:

Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = UCase(Range("E5"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)
End If
Next Cell
End Sub


----------



## budward (Oct 28, 2006)

thanks bomb#21. 
Could I get it to return the itemKey and the Description?
350-0008-1	GEM VAR WHITE SATIN TINT BASE (1 GL)
350-0008-5	GEM VAR WHITE SATIN TINT BASE (5 GL)


----------



## bomb #21 (Jul 1, 2005)

In a single cell (per Key and Desc)?


----------



## budward (Oct 28, 2006)

Hi bomb321:
ItemKey..................................... Desc ...................................................... Price
350-0008-1 in E12......... GEM VAR WHITE SATIN TINT BASE (1 GL) in F12....... 1.99 in G12
Also I will be adding a Col C with prices and I would like prices returned in G12. This thing keeps growing don't it? haha


----------



## budward (Oct 28, 2006)

Hi bomb;
I added two more lines to the macro
Range("E" & Rows.Count).End(xlUp).Offset(0, 1) = Cell.Offset(, -0)
Range("E" & Rows.Count).End(xlUp).Offset(0, 2) = Cell.Offset(, 1)
and it gives the ItemKey, Desc, $ price.
Thanks. Is this the best way to do this?
Bud


----------



## bomb #21 (Jul 1, 2005)

Replace:

Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)

with:

Range("E" & Rows.Count).End(xlUp).Offset(1, 0) = Cell.Offset(, -1)
Range("E" & Rows.Count).End(xlUp).Offset(0, 1) = Cell
Range("E" & Rows.Count).End(xlUp).Offset(0, 2) = Cell.Offset(, 1)

Be *very* careful. If you end up with no (column) gaps (between original data & returned data), you'll have to take out Range("E11").CurrentRegion.ClearContents.


----------



## budward (Oct 28, 2006)

guess what? What I did doesn't work. It returns the whole ItemKey,Desc, and Price Lit. It in effect copies the first 3 columns and doesn't search anything.
Help me bomb#21.
LoL..Bud
I will try your new answer. What I did was terrible.


----------



## budward (Oct 28, 2006)

Hey,GOOD JOB! bomb.
I got exactly what I need now.
Thanks again...Bud


----------



## bomb #21 (Jul 1, 2005)

You gave me an idea when you mentioned "copy". Try:

Sub Search()
Range("E11").CurrentRegion.ClearContents: Range("E11") = "Results"
FindWhat = UCase(Range("E5"))
For Each Cell In Range("B2", Range("B" & Rows.Count).End(xlUp))
If InStr(Cell, FindWhat) <> 0 Then
Cell.Offset(, -1).Resize(, 3).Copy Range("E" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next Cell
End Sub

(less hoops to jump thru)


----------



## budward (Oct 28, 2006)

Ok, I will try the new one also.
Bud


----------

