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

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