Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Difficulties W/ Sum For Variable-length Column
Dave and Patrick, Thanks very much for your responses. However, my macro is still no working. I appreciate the comment about mixing relative and absolute addressin in my formula; but it appears to be a non-fatal error. I say thi because I was able to get the formula = SUM(J3:J99) in the appropriat cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was abl to get the numerical result to display. Which, part of my original question, is why can I not send the {F2} ke from the macro to the WorkSheet (as opposed to opening the Objec Browser)? Patrick, I like your approach because it does not require insertion o the Row number of the cell adjacent above the sum formula in a cel away from the range to be summed. But I get an "Application-Defined o object-defined error" message when using your Target.FormulaR1C1 "=SUM(R3C:R[-1]C". Dave, I am using the row 4 rows down from the sum formula to displa the row number of the cell adjacent above the sum formula (the Activ Cell). In your ActiveCell.Formula, the compiler is having difficult with the .Offset. I would appreciate your continuing help. Chuckles123 P.S.: A related issue is the ability to name a variable-length colum or row in a macro. Any thoughts -- Chuckles12 ----------------------------------------------------------------------- Chuckles123's Profile: http://www.excelforum.com/member.php...fo&userid=1494 View this thread: http://www.excelforum.com/showthread.php?threadid=26589 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Difficulties W/ Sum For Variable-length Column
sorry - typo
"=SUM(R3C:R[-1]C)" I missed the close bracket for the SUM function please stay in-thread :) -- Patrick Molloy Microsoft Excel MVP --------------------------------- I Feel Great! --------------------------------- "Chuckles123" wrote in message ... Dave and Patrick, Thanks very much for your responses. However, my macro is still not working. I appreciate the comment about mixing relative and absolute addressing in my formula; but it appears to be a non-fatal error. I say this because I was able to get the formula = SUM(J3:J99) in the appropriate cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was able to get the numerical result to display. Which, part of my original question, is why can I not send the {F2} key from the macro to the WorkSheet (as opposed to opening the Object Browser)? Patrick, I like your approach because it does not require insertion of the Row number of the cell adjacent above the sum formula in a cell away from the range to be summed. But I get an "Application-Defined or object-defined error" message when using your Target.FormulaR1C1 = "=SUM(R3C:R[-1]C". Dave, I am using the row 4 rows down from the sum formula to display the row number of the cell adjacent above the sum formula (the Active Cell). In your ActiveCell.Formula, the compiler is having difficulty with the .Offset. I would appreciate your continuing help. Chuckles123 P.S.: A related issue is the ability to name a variable-length column or row in a macro. Any thoughts? -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=265895 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Difficulties W/ Sum For Variable-length Column
Variable range
=A1:OFFSET(A1,,,COUNTA(A:A)) -- HTH RP "Chuckles123" wrote in message ... Dave and Patrick, Thanks very much for your responses. However, my macro is still not working. I appreciate the comment about mixing relative and absolute addressing in my formula; but it appears to be a non-fatal error. I say this because I was able to get the formula = SUM(J3:J99) in the appropriate cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was able to get the numerical result to display. Which, part of my original question, is why can I not send the {F2} key from the macro to the WorkSheet (as opposed to opening the Object Browser)? Patrick, I like your approach because it does not require insertion of the Row number of the cell adjacent above the sum formula in a cell away from the range to be summed. But I get an "Application-Defined or object-defined error" message when using your Target.FormulaR1C1 = "=SUM(R3C:R[-1]C". Dave, I am using the row 4 rows down from the sum formula to display the row number of the cell adjacent above the sum formula (the Active Cell). In your ActiveCell.Formula, the compiler is having difficulty with the .Offset. I would appreciate your continuing help. Chuckles123 P.S.: A related issue is the ability to name a variable-length column or row in a macro. Any thoughts? -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=265895 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Continuing Difficulties W/ Sum For Variable-length Column
I had assumed that you were creating a name to add this as a RefersTo value.
Probably also better to use =$A$1:OFFSET($A$1,,,COUNTA($A:$A)) -- HTH RP "Bob Phillips" wrote in message ... Variable range =A1:OFFSET(A1,,,COUNTA(A:A)) -- HTH RP "Chuckles123" wrote in message ... Dave and Patrick, Thanks very much for your responses. However, my macro is still not working. I appreciate the comment about mixing relative and absolute addressing in my formula; but it appears to be a non-fatal error. I say this because I was able to get the formula = SUM(J3:J99) in the appropriate cell, which, in the WorkSheet, by keying {F2} and {ENTER}, I was able to get the numerical result to display. Which, part of my original question, is why can I not send the {F2} key from the macro to the WorkSheet (as opposed to opening the Object Browser)? Patrick, I like your approach because it does not require insertion of the Row number of the cell adjacent above the sum formula in a cell away from the range to be summed. But I get an "Application-Defined or object-defined error" message when using your Target.FormulaR1C1 = "=SUM(R3C:R[-1]C". Dave, I am using the row 4 rows down from the sum formula to display the row number of the cell adjacent above the sum formula (the Active Cell). In your ActiveCell.Formula, the compiler is having difficulty with the .Offset. I would appreciate your continuing help. Chuckles123 P.S.: A related issue is the ability to name a variable-length column or row in a macro. Any thoughts? -- Chuckles123 ------------------------------------------------------------------------ Chuckles123's Profile: http://www.excelforum.com/member.php...o&userid=14948 View this thread: http://www.excelforum.com/showthread...hreadid=265895 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sum a Column of Variable length | Excel Discussion (Misc queries) | |||
Sum a column of variable length? | Excel Discussion (Misc queries) | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming | |||
Using a Macro to Sum a Variable-Length Range (a Column) | Excel Programming | |||
Averaging a variable length column | Excel Programming |