Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: How to search and then paste elsewhere
Hello I'm trying to use a macro to reformat a spreadsheet automatically. My ultimate goal is to search for the phrase "Transaction Type" an copy and paste it over 4 cells to the right. How can I use a variable in a macro to accomplish this? I recorded the following macro to trace my steps but how do I make mor generic in case the field is not in cell B211? Any help would be appreciated Thanks Bob Sub Macro6() ' ' Macro6 Macro ' Macro recorded 10/21/2005 by Robert Belforti ' ' Columns("B:B").Select Selection.Find(What:="Transaction Type", After:=ActiveCell LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns SearchDirection:= _ xlNext, MatchCase:=False).Activate Selection.FindNext(After:=ActiveCell).Activate Range("B211").Select Selection.Cut Range("E211").Select ActiveSheet.Paste End Su -- rbelfort ----------------------------------------------------------------------- rbelforti's Profile: http://www.excelforum.com/member.php...fo&userid=1106 View this thread: http://www.excelforum.com/showthread.php?threadid=47839 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro: How to search and then paste elsewhere
It's usually nice to set a range variable to the results of the .find. Then you
can check to see if it was found--and use that to determine where to paste. Option Explicit Sub Macro6b() dim FoundCell as range dim myRng as range with activesheet set myrng = .columns("B:B") with myrng set foundcell = .cells.find(What:="Transaction Type", _ After:=.cells(.cells.count), LookIn:=xlFormulas, _ LookAt:=xlPart, SearchOrder:=xlByColumns, _ SearchDirection:=xlNext, MatchCase:=False) end with end with if foundcell is nothing then 'not found else foundcell.cut _ destination:=foundcell.offset(0,3) end if End Sub rbelforti wrote: Hello I'm trying to use a macro to reformat a spreadsheet automatically. My ultimate goal is to search for the phrase "Transaction Type" and copy and paste it over 4 cells to the right. How can I use a variable in a macro to accomplish this? I recorded the following macro to trace my steps but how do I make more generic in case the field is not in cell B211? Any help would be appreciated Thanks Bob Sub Macro6() ' ' Macro6 Macro ' Macro recorded 10/21/2005 by Robert Belforti ' ' Columns("B:B").Select Selection.Find(What:="Transaction Type", After:=ActiveCell, LookIn:= _ xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:= _ xlNext, MatchCase:=False).Activate Selection.FindNext(After:=ActiveCell).Activate Range("B211").Select Selection.Cut Range("E211").Select ActiveSheet.Paste End Sub -- rbelforti ------------------------------------------------------------------------ rbelforti's Profile: http://www.excelforum.com/member.php...o&userid=11064 View this thread: http://www.excelforum.com/showthread...hreadid=478395 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Search, cut & paste | Excel Worksheet Functions | |||
Search, copy and paste help | Excel Discussion (Misc queries) | |||
Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
search/copy/paste macro | Excel Programming | |||
Search/copy/paste Macro | Excel Programming |