Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

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

Loop through the columns and extract the matches
 

Can someone help? The post above stated that I was not specific enough
so I provided the file as an example thinking this would help. Anybody?


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

Loop through the columns and extract the matches
 

I looked at your file, and the original macro I wrote will work fine with
the data you show. I have revised it slightly so it isn't dependent on the
values of Edit=Find. I highlighted a word in column G and ran the macro
and it put the values of cells that contained that word in column M,
starting in row 3.


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, _
LookIn:=xlValues, Lookat:=xlPart)
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

--
Regards,
Tom Ogilvy


"ebraun01" wrote in
message ...

Can someone help? The post above stated that I was not specific enough
so I provided the file as an example thinking this would help. Anybody?


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

Loop through the columns and extract the matches
 

This is a very good start in the right direction and really appreciate
your efforts. What I would like to so is run the macro and have it copy
all matching cells into one cell. I have attached a file that gives an
example of what I'm talking about. The copied files are in column K in
this example.

This would keep me from running the macro many times and cut and
pasting each match into a single cell.

Again, thanks for your assitance, this is a life saver.


+-------------------------------------------------------------------+
|Filename: Daily Double 0212 - 0218 test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4363 |
+-------------------------------------------------------------------+

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

Loop through the columns and extract the matches
 

Sub ABCD()
Dim rng As Range, rng1 As Range
Dim i As Long, sAddr As String
Dim s as String
Set rng = ActiveCell
Set rng1 = Range("A:F").Find(rng, _
LookIn:=xlValues, Lookat:=xlPart)
If Not rng1 Is Nothing Then
sAddr = rng1.Address
Do
s = s & rng1.Value & vbNewLine
Set rng1 = Range("A:F").FindNext(rng1)
Loop While rng1.Address < sAddr
End If
s = left(s,len(s)-len(vbNewLine))
cells(activecell.Value,"K").Value = s
End Sub

--
regards,
Tom Ogilvy


"ebraun01" wrote in
message ...

This is a very good start in the right direction and really appreciate
your efforts. What I would like to so is run the macro and have it copy
all matching cells into one cell. I have attached a file that gives an
example of what I'm talking about. The copied files are in column K in
this example.

This would keep me from running the macro many times and cut and
pasting each match into a single cell.

Again, thanks for your assitance, this is a life saver.


+-------------------------------------------------------------------+
|Filename: Daily Double 0212 - 0218 test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4363 |
+-------------------------------------------------------------------+

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

Loop through the columns and extract the matches
 

Attached is the file with the macro added. I get a mismatch error when I
try to run it.


+-------------------------------------------------------------------+
|Filename: Daily Double 0212 - 0218 test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4366 |
+-------------------------------------------------------------------+

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

Loop through the columns and extract the matches
 

My typo

Change
Cells(ActiveCell.Value, "K").Value = s

to

Cells(ActiveCell.row, "K").Value = s

--
Regards,
Tom Ogilvy


"ebraun01" wrote in
message ...

Attached is the file with the macro added. I get a mismatch error when I
try to run it.


+-------------------------------------------------------------------+
|Filename: Daily Double 0212 - 0218 test.zip |
|Download: http://www.excelforum.com/attachment.php?postid=4366 |
+-------------------------------------------------------------------+

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

Loop through the columns and extract the matches
 

That is aweome!!

Now, one last thing.. is there a way for me to not have to run the
macro for each line? If not, that's fine. This works the way it is, I
just have to highlite each line then run the macro, which still saves
me a lot of time. But if there is a way to run the macro and all the
matching lines for each match paste into their cells, that would be
very nice.


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

Loop through the columns and extract the matches
 

Any suggestion on the last part? Just need to have the macro run for
each match, if it's possible.


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

Loop through the columns and extract the matches
 

Anybody else then? I'm wanting to use the macro provided above. Which
works great on a single cell, but have it run on multiple cells
automatically. The current macro allows me to highlight a word, then it
will search the database for cells with that word and copy/paste all
matching cells into a new cell. I then highlight another word in the
column and repeat the process. I would like it to go down the column
and do all words that have a match automatcialy, basically running the
macro above over and over.


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