Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
I wrote the following macro and assigned it to a button a few years ago, I
don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
SendKeys up = Activecell.Offset(0,-1).Select
SendKeys Right = Activecell.Offset(1,0).Select So, your code can be mostly replaced with one line: Activecell.Offset(11,-1).Select As for the ^c line, I think what you're doing there is copying the contents of the cell you are on and then moving down one cell and pasting the result. This translates into: Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste "EB" wrote: I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Application.Goto Reference:="e" is an invalid range.
Application.Goto Reference:=Range("e2") selects cell E2 Do you know if "e" is a defined range name in your spreadsheet? Do you know what cell the macro should start at? and the other 2 cell locations that are listed in the macro? "EB" wrote: I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
Thanks. I will try that.
"BobT" wrote in message ... SendKeys up = Activecell.Offset(0,-1).Select SendKeys Right = Activecell.Offset(1,0).Select So, your code can be mostly replaced with one line: Activecell.Offset(11,-1).Select As for the ^c line, I think what you're doing there is copying the contents of the cell you are on and then moving down one cell and pasting the result. This translates into: Selection.Copy ActiveCell.Offset(1, 0).Select ActiveSheet.Paste "EB" wrote: I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macros
e is a defined range of 1 cell. The reaso for using it is that when the
macro adds a line the range automatically advances appropriately. I couldn't find another way of doing it. Thanks "DataHog" wrote in message ... Application.Goto Reference:="e" is an invalid range. Application.Goto Reference:=Range("e2") selects cell E2 Do you know if "e" is a defined range name in your spreadsheet? Do you know what cell the macro should start at? and the other 2 cell locations that are listed in the macro? "EB" wrote: I wrote the following macro and assigned it to a button a few years ago, I don't know under which version of Excel. It still works. I created it by entering commands manualy and having it recorded as a macro. =========== Sub Button9_Click() Application.Goto Reference:="e" Selection.EntireRow.Insert SendKeys "{UP}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}+{RIGHT}", True SendKeys "+{RIGHT}", True SendKeys "^{c}{DOWN}{ENTER}", True Application.Goto Reference:="e" SendKeys "{DOWN}{RIGHT}{RIGHT}", True ActiveCell.FormulaR1C1 = "=RC[-1]+R[-2]C[5]" Application.Goto Reference:="e" SendKeys "{UP}", True End Sub =========== 1) I can run the macro under XP. 2) I cannot run it under Vista. As soon as it hits the first SendKey, it complains about some security breach. 3) I can not rerecird it from scratch under any new Excel (Vista or XP). It doesn't generate SendKeys. It just moves to the final cell. Which is useless because I'd like to get there dynamiccaly based on the orig cell. Any help will be appriciated. EB |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macros in Personal.xls that would create two toolbars and buttonswith assigned macros | Excel Programming | |||
choose default macros Not Enabled / Macros Enable Setting | Excel Programming | |||
weird saving of a document with macros resulting with macros being transfered to the copy | Excel Programming | |||
Macros inside macros, and pasting into macro code. | Excel Programming | |||
Open workbook-macros enabled, opening another with macros | Excel Programming |