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 |
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 |
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 11:47 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com