Macros

[EB]

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

 

[BobT]

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

 

[DataHog]

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]

Thanks. I will try that.

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

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

 

[EB]

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

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?

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