I have a spreadsheet that allows me to place a word in one cell and i
searches for that word in several columns. When it finds a match i
highlight the cell with the matched word. What I would like, rathe
than highlight the cell, is to have the speadsheet copy the matche
cell into a cell in another column. If there are multiple matches, i
would copy all of them into a cell.

Any suggestions

--
ebraun0
-----------------------------------------------------------------------
ebraun01's Profile: http://www.excelforum.com/member.php...fo&userid=3034
View this thread: http://www.excelforum.com/showthread.php?threadid=50633

Dim rng as Range, rng1 as Range
Dim i as Long, sAddr as String
set rng = ActiveCell
set rng1 = Range("B:F").find(rng)
i = 3
if not rng1 is nothing then
saddr = rng1.Address
do
cells(i,"M").Value = rng1
i = i + 1
set rng1 = Range("B:F").FindNext(rng1)
loop while rng1.Address < saddr
End if

--
Regards,
Tom Ogilvy


"ebraun01" wrote in
message ...

I have a spreadsheet that allows me to place a word in one cell and it
searches for that word in several columns. When it finds a match it
highlight the cell with the matched word. What I would like, rather
than highlight the cell, is to have the speadsheet copy the matched
cell into a cell in another column. If there are multiple matches, it
would copy all of them into a cell.

Any suggestions?


--
ebraun01
------------------------------------------------------------------------
ebraun01's Profile:
http://www.excelforum.com/member.php...o&userid=30340
View this thread: http://www.excelforum.com/showthread...hreadid=506336

When I run this script I receive a compile error "Invalid Outside
Procedure" with the word "Set" highlighted. Any suggestions? Thanks for
helping.


--
ebraun01
------------------------------------------------------------------------
ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340
View this thread: http://www.excelforum.com/showthread...hreadid=506336

Well, you have to put it in a procedure

Sub ABC()
Dim rng as Range, rng1 as Range
Dim i as Long, sAddr as String
set rng = ActiveCell
set rng1 = Range("B:F").find(rng)
i = 3
if not rng1 is nothing then
saddr = rng1.Address
do
cells(i,"M").Value = rng1
i = i + 1
set rng1 = Range("B:F").FindNext(rng1)
loop while rng1.Address < saddr
End if
End Sub

And obviously, since you specified nothing specific in your description, the
ranges used in the macro are notional and would need to be changed to fit
your situation.

--
Regards,
Tom Ogilvy


"ebraun01" wrote in
message ...

When I run this script I receive a compile error "Invalid Outside
Procedure" with the word "Set" highlighted. Any suggestions? Thanks for
helping.


--
ebraun01
------------------------------------------------------------------------
ebraun01's Profile:
http://www.excelforum.com/member.php...o&userid=30340
View this thread: http://www.excelforum.com/showthread...hreadid=506336

I very much appreciate the time you have taken to assist me. I was not
to to specific because it does get a bit confusing to write but I will
try so that I can put your script to good use.

I am attaching a copy of my speadsheet so you have a specific example
to work from. Basically there are 5 columns A-E with coupon data. Colum
F is updated weekly. Column G runs a formula that takes the first word
from each cell in column F and shows me how many matches there are
within columns A-E (column H). Column I allows me to manually type in a
matching word and the matching cells within A-E will conditionally
change color to make it easier for me to find them. I then cut and
paste the matching cells into column K.

What I am trying to do is eliminate the last step. Rather than having
to cut and paste the matching cells. I would like to run a
script/formula that would find the matches as it already does but
instead of coloring them conditionally, automatically cut/paste them
into a cell in K.

Sorry if that is a bit confusing. The whole point of the spreadsheet is
to take things that are on sale this week in one store, and bounce them
off a set of manufacturer coupons to get an even better deal than the
original sale.


+-------------------------------------------------------------------+
|Filename: Sunday Sales 0204 - 0211.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4307 |
+-------------------------------------------------------------------+

--
ebraun01
------------------------------------------------------------------------
ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340
View this thread: http://www.excelforum.com/showthread...hreadid=506336

Any Suggestions?


--
ebraun01
------------------------------------------------------------------------
ebraun01's Profile: http://www.excelforum.com/member.php...o&userid=30340
View this thread: http://www.excelforum.com/showthread...hreadid=506336