ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   macro doesn't properly record AutoSum (and SendKeys doesn't work) (https://www.excelbanter.com/excel-programming/377789-macro-doesnt-properly-record-autosum-sendkeys-doesnt-work.html)

crimsonkng November 20th 06 08:03 PM

macro doesn't properly record AutoSum (and SendKeys doesn't work)
 
In my macro, I'm trying to "insert" an auto-sum command into various,
periodic cells. But when I record the keystrokes it always refers to a range
of cells (whether it's a relative or absolute address). So, that won't work
because it's a different range every time. I just want to execute an
auto-sum function into a balnk cell. But even SendKeys doesn't work. The
macro ignores the SendKeys command of: "alt-equals-enter."

Here's a description, in general:

In Column B, I have thousands of dollar-amounts, separated intermittently by
two blank cells. I need to sum the range of cells directly "above" the first
blank cell. My macro can "find" each blank cell in which the auto-sum should
occur -- that's no problem. But when I'm at the blank cell, I merely want to
perform an auto-sum. But I can't specify an exact range because the range
(number of cells) changes every time. So, in effect, I just want to "click"
the auto-sum icon. But, like I said above, "record macro" inserts the
specific row addresses (or relative range) and SendKeys doesn't work.

So, how can I program the macro to perform an auto-sum without specifying a
specific range of cells?

Thanks. Dan

Jim Rech November 20th 06 10:15 PM

macro doesn't properly record AutoSum (and SendKeys doesn't work)
 
Sub SumAbove()
With ActiveCell
.Formula = "=sum(" & Range(.Offset(-1), _
.Offset(-1).End(xlUp)).Address(False, False) & ")"
End With
End Sub

--
Jim
"crimsonkng" wrote in message
...
| In my macro, I'm trying to "insert" an auto-sum command into various,
| periodic cells. But when I record the keystrokes it always refers to a
range
| of cells (whether it's a relative or absolute address). So, that won't
work
| because it's a different range every time. I just want to execute an
| auto-sum function into a balnk cell. But even SendKeys doesn't work. The
| macro ignores the SendKeys command of: "alt-equals-enter."
|
| Here's a description, in general:
|
| In Column B, I have thousands of dollar-amounts, separated intermittently
by
| two blank cells. I need to sum the range of cells directly "above" the
first
| blank cell. My macro can "find" each blank cell in which the auto-sum
should
| occur -- that's no problem. But when I'm at the blank cell, I merely want
to
| perform an auto-sum. But I can't specify an exact range because the range
| (number of cells) changes every time. So, in effect, I just want to
"click"
| the auto-sum icon. But, like I said above, "record macro" inserts the
| specific row addresses (or relative range) and SendKeys doesn't work.
|
| So, how can I program the macro to perform an auto-sum without specifying
a
| specific range of cells?
|
| Thanks. Dan



crimsonkng November 21st 06 02:11 PM

macro doesn't properly record AutoSum (and SendKeys doesn't wo
 
Jim:

You are awesome. Thank you so much!

Dan

"Jim Rech" wrote:

Sub SumAbove()
With ActiveCell
.Formula = "=sum(" & Range(.Offset(-1), _
.Offset(-1).End(xlUp)).Address(False, False) & ")"
End With
End Sub

--
Jim
"crimsonkng" wrote in message
...
| In my macro, I'm trying to "insert" an auto-sum command into various,
| periodic cells. But when I record the keystrokes it always refers to a
range
| of cells (whether it's a relative or absolute address). So, that won't
work
| because it's a different range every time. I just want to execute an
| auto-sum function into a balnk cell. But even SendKeys doesn't work. The
| macro ignores the SendKeys command of: "alt-equals-enter."
|
| Here's a description, in general:
|
| In Column B, I have thousands of dollar-amounts, separated intermittently
by
| two blank cells. I need to sum the range of cells directly "above" the
first
| blank cell. My macro can "find" each blank cell in which the auto-sum
should
| occur -- that's no problem. But when I'm at the blank cell, I merely want
to
| perform an auto-sum. But I can't specify an exact range because the range
| (number of cells) changes every time. So, in effect, I just want to
"click"
| the auto-sum icon. But, like I said above, "record macro" inserts the
| specific row addresses (or relative range) and SendKeys doesn't work.
|
| So, how can I program the macro to perform an auto-sum without specifying
a
| specific range of cells?
|
| Thanks. Dan





All times are GMT +1. The time now is 09:26 PM.

Powered by vBulletin® Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
ExcelBanter.com