Macro: How to search and then paste elsewhere

[rbelforti]

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

 

[Dave Peterson]

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