Macro to F2, F9, then cut and paste

[GwenH]

I'm using Excel 2007, and I'm having problems with something that was simple
in Excel 2002. I have three cells in three rows. After I move to the third
cell in the third row, I need to invoke a macro that presses F2, F9, then
ENTER in that same cell. Then I need the macro hit the up arrow once, to go
up one row and back to the cell in the third row. Finally, I need to cut the
resulting contents of the third cell, and paste them into the cell two rows
above it (the cell in row 1). Then I need to move down one row (to the cell
in the second row) and delete the contents from that second cell.

When I record a macro in 2007, it wants to go back to the cells I edited
when I first recorded the macro. I don't want it to do that. In the code
below, specific cells are specified - B3, B4, and B5. Rather than always
running the macro on these three cells, I need it to work on the cell I have
currently selected, as well as the two cells immediately above it. How do I
need to change the code of this macro so it does that?

Thanks,
GwenH

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+a
'
ActiveCell.FormulaR1C1 = "2.1–1 Control Rod Guide Tube Assembly"
With ActiveCell.Characters(Start:=1, Length:=37).Font
.Name = "Helvetica"
.FontStyle = "Regular"
.Size = 9
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ThemeColor = xlThemeColorLight1
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Range("B5").Select
Selection.Cut
Range("B3").Select
ActiveSheet.Paste
Range("B4").Select
Selection.ClearContents
End Sub

 

[David Sauder]

I'm sure this could be accomplished using the kind of code you have
here, from the macro recorder. To make it work as you want you would
have to set the recorder to "relative" reference rather than
"absolute" reference. However, as is often the case the recorded code
is much longer and more complex than it needs to be. Here is a short
sub that will accomplish what I think you are trying to do:

Sub DoIt()
'copies value from current cell to cell two rows above, then
clears current cell and cell above it

'check to make sure there is room to copy value up two rows
If ActiveCell.Row < 3 Then
MsgBox "Can't do that here!", vbOKOnly, ""
Exit Sub
End If

'copy value from current row to row two above
ActiveCell.Offset(-2, 0).Value = ActiveCell.Value

'delete value from current cells
ActiveCell.ClearContents

'delete value from above cell
ActiveCell.Offset(-1, 0).ClearContents

End Sub