Using a Macro to Sum a Variable-Length Range (a Column)
 

Dave,

Point well taken -- I agree; it is the mixing of R1C1 and A1 that i
the issue.

Thanks to you, I now know that SendKeys does not work when using VBE.
By the way, why is there one button for "Step Into" and one button fo
"Edit" when wanting to look at coding?

In my original macro, I inserted the formula =ROW(R[-5]C) in the cell
rows below the cell to contain the SUM formula (in order to capture th
end of the desired range). This cell was subsequently cleared in th
coding.

Thanks again for all your help,
Chuckles12

--
Chuckles12
-----------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494
View this thread: http://www.excelforum.com/showthread.php?threadid=26581

Using a Macro to Sum a Variable-Length Range (a Column)
 

Edit just takes you to that procedure in the code window. Step Into begins to
execute the code, one line at a time.

On Sun, 3 Oct 2004 22:56:46 -0500, Chuckles123
wrote:


Dave,

Point well taken -- I agree; it is the mixing of R1C1 and A1 that is
the issue.

Thanks to you, I now know that SendKeys does not work when using VBE.
By the way, why is there one button for "Step Into" and one button for
"Edit" when wanting to look at coding?

In my original macro, I inserted the formula =ROW(R[-5]C) in the cell 4
rows below the cell to contain the SUM formula (in order to capture the
end of the desired range). This cell was subsequently cleared in the
coding.

Thanks again for all your help,
Chuckles123

Using a Macro to Sum a Variable-Length Range (a Column)
 

So all you want is to sum from J3:J(one row above)?

Then how about:

With ActiveCell
.Formula = "=sum(J3:J" & .Row - 1 & ")"
.Value = .Value 'convert to values
End With

(I wasn't sure if you got your code working.)

Chuckles123 wrote:

Dave,

Point well taken -- I agree; it is the mixing of R1C1 and A1 that is
the issue.

Thanks to you, I now know that SendKeys does not work when using VBE.
By the way, why is there one button for "Step Into" and one button for
"Edit" when wanting to look at coding?

In my original macro, I inserted the formula =ROW(R[-5]C) in the cell 4
rows below the cell to contain the SUM formula (in order to capture the
end of the desired range). This cell was subsequently cleared in the
coding.

Thanks again for all your help,
Chuckles123

--
Chuckles123
------------------------------------------------------------------------
Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948
View this thread: http://www.excelforum.com/showthread...hreadid=265814

--

Dave Peterson