Macro: search word, select row, copy.

[Guest]

I'm tryingo to write a macro in excel which:

-Finds a word in the file
-Selects the row which contains the word
-Changes the color of the row
-Copy the entire row

I have this:

Cells.FindNext(After:=ActiveCell).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Copy
End Sub


but i can't seem to figure out how to select only the row which contains the
word. If I select the entire row, the macro will always copy that same row ['
Rows(4)' will select always row 4. 'Rows' will select all the rows...]

Comments?

 

[Dave Peterson]

Finds a word in a worksheet??

dim FoundCell as Range
dim WhatToFind as string

whattofind = "SomeWordHere"

with worksheets("sheet9999")
set foundcell = .cells.find(what:=whattofind, _
after:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
lookat:=xlWhole, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)
end with

if foundcell is nothing then
'the word wasn't found
else
foundcell.entirerow.interior.colorindex = 3 'what color do you want??
foundcell.entirerow.copy _
destination:=someothercellincolumnA
end if

====
Untested and uncompiled.

I'm tryingo to write a macro in excel which:

-Finds a word in the file
-Selects the row which contains the word
-Changes the color of the row
-Copy the entire row

I have this:

Cells.FindNext(After:=ActiveCell).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Copy
End Sub

but i can't seem to figure out how to select only the row which contains the
word. If I select the entire row, the macro will always copy that same row ['
Rows(4)' will select always row 4. 'Rows' will select all the rows...]

Comments?

 

[Vasant Nanavati]

Not sure I completely understand, but here goes:

With Cells.FindNext(ActiveCell).EntireRow
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
.Copy
End With

Unless there is a rare and specific need, there is no need to select the
row.
________________________________________________________________________

 

[Guest]

Thanks! It works, but not quite the way i need it to.

What i need is this: i have a file in excel which contains a list of many
components. I have to find a specific component, change the color of the row
and copy it. The idea is to assign the macro to a button.

The problem with your answer is that when i press the macro button, it only
works fine the first time. Then i have to click the next cell and then press
again the macro button. The idea is to only press the button, store the
copied rows in the clipboard and then paste them in another place (which i'll
do manually).

I have problems with selecting the entire row. If i write the entire row
command, an error will occur. If i write a number (eg. row 7), that row
number will always be selected.

Any idea on how to solve the problem? Thanks!

Raul

Not sure I completely understand, but here goes:

With Cells.FindNext(ActiveCell).EntireRow
.Interior.ColorIndex = 6
.Interior.Pattern = xlSolid
.Copy
End With

Unless there is a rare and specific need, there is no need to select the
row.
________________________________________________________________________

I'm tryingo to write a macro in excel which:

-Finds a word in the file
-Selects the row which contains the word
-Changes the color of the row
-Copy the entire row

I have this:

Cells.FindNext(After:=ActiveCell).Activate
With Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Copy
End Sub


but i can't seem to figure out how to select only the row which contains
the
word. If I select the entire row, the macro will always copy that same row
['
Rows(4)' will select always row 4. 'Rows' will select all the rows...]

Comments?