Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: search word, select row, copy.
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: search word, select row, copy.
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. Raul wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: search word, select row, copy.
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. __________________________________________________ ______________________ "Raul" wrote in message ... 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: search word, select row, copy.
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 "Vasant Nanavati" wrote: 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. __________________________________________________ ______________________ "Raul" wrote in message ... 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search Excel and copy result into word | Excel Programming | |||
macro to intsert a word using the "Find" command to search for a word | Excel Programming | |||
macro to search for & select ranges to sum | Excel Programming | |||
search for a specific word and copy the word and the preceeding words until a comma | Excel Programming | |||
select all & copy in word from macro in excel | Excel Programming |