Macro to F2, F9, then cut and paste
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 |
Macro to F2, F9, then cut and paste
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 On Mar 2, 3:50 pm, GwenH wrote: 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 |
All times are GMT +1. The time now is 04:26 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com