|
Entering a Sum Formula in VBA with variables
I need to enter a sum formula by code.
I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
ActiveCell.FormulaR1C1="=sum(R[" & subrow1 & "]C[" & subcol1 & "]):R[" &
subrow2 & "]C[" subcol2 & "])" Not tested but follow this style! Regards, Stefi €˛Sue€¯ ezt Ć*rta: I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" & subcol2 & "])" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined error. What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. "Bob Phillips" wrote: ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" & subcol2 & "])" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
Thankyou but I still have the same problem. It compiles but won't run. It
comes up with Run-time error 1004 - Application-defined or object-defined error. What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. "Stefi" wrote: ActiveCell.FormulaR1C1="=sum(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" subcol2 & "])" Not tested but follow this style! Regards, Stefi €˛Sue€¯ ezt Ć*rta: I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
I have the same problem!
From the immediate window I can fill the formula and it works fine, but from a Sub I get Application-defined error. I have use intermediate text strings, displayed them before putting them in a worksheet, compared them with the formula bar (switched to R1C1 reference style) ............ Can't get it done thru code! Please keep us informed here about any progress made -- Kind regards, Niek Otten Microsoft MVP - Excel "Sue" wrote in message ... | Thankyou but I still have the same problem. It compiles but won't run. It | comes up with Run-time error 1004 - Application-defined or object-defined | error. | What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and | ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. | | "Bob Phillips" wrote: | | | ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & | subrow2 & "]C[" & subcol2 & "])" | | | -- | --- | HTH | | Bob | | | (there's no email, no snail mail, but somewhere should be gmail in my addy) | | | | "Sue" wrote in message | ... | I need to enter a sum formula by code. | I can establish the coordinates of the range in R1C1 form. | Say the points are (subrow1,subcol1) and (subrow2,subcol2). | I can get where I want to put the formula. | I have tried: | ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) | or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) | - as the column is adjacent - and variations on the theme but everything | with a variable in it produces a run time error. | I need the total of a variable amount of the column (which is adjacent to | where the total is required.) The formula can not be turned into a fixed | offset but must be a range from the two points. Help! | | | | |
Entering a Sum Formula in VBA with variables
This works for me
Dim mpFormula As String mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _ subrow2 & "]C[" & subcol2 & "])" ActiveCell.FormulaR1C1 = mpFormula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Thankyou but I still have the same problem. It compiles but won't run. It comes up with Run-time error 1004 - Application-defined or object-defined error. What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. "Bob Phillips" wrote: ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" & subcol2 & "])" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
Hi Bob, thanks very much for your help. I have worked hard on this problem
and I got a result (the correct answers) with this code: Range("A1").Offset(subrow1 - 1, subcol1).Select mpFormula = "=SUM(R[" & subrow1 - subrow1 & "]C[" & subcol1 - _ subcol1 - 1 & "]:R[" & subrow2 - subrow1 & "]C[" & subcol2 - _ subcol1 - 1 & "])" ActiveCell.FormulaR1C1 = mpFormula BUT I have established subrow1, subcol1, etc as R1C1 coordinates of cells I want to reference and Excel seems only to use these as offsets. This makes for very clumsy and obscure code. Is there a way I can go to R[subrow1]C[subcol1+1] and put the formula sum(R[subrow1]C[subcol1]:R[subrow2]C[subcol2]). Does this need an absolute reference? I've tried but cannot get the syntax right. "Bob Phillips" wrote: This works for me Dim mpFormula As String mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _ subrow2 & "]C[" & subcol2 & "])" ActiveCell.FormulaR1C1 = mpFormula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Thankyou but I still have the same problem. It compiles but won't run. It comes up with Run-time error 1004 - Application-defined or object-defined error. What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. "Bob Phillips" wrote: ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" & subcol2 & "])" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! |
Entering a Sum Formula in VBA with variables
maybe...
myformula = "=sum(r[" & subrow1 & "]c[" & subcol1 _ & "]:r[" & subrow2 & "]c[" & subcol2 & "])" But I'm kind of confused about what you really want. Sue wrote: Hi Bob, thanks very much for your help. I have worked hard on this problem and I got a result (the correct answers) with this code: Range("A1").Offset(subrow1 - 1, subcol1).Select mpFormula = "=SUM(R[" & subrow1 - subrow1 & "]C[" & subcol1 - _ subcol1 - 1 & "]:R[" & subrow2 - subrow1 & "]C[" & subcol2 - _ subcol1 - 1 & "])" ActiveCell.FormulaR1C1 = mpFormula BUT I have established subrow1, subcol1, etc as R1C1 coordinates of cells I want to reference and Excel seems only to use these as offsets. This makes for very clumsy and obscure code. Is there a way I can go to R[subrow1]C[subcol1+1] and put the formula sum(R[subrow1]C[subcol1]:R[subrow2]C[subcol2]). Does this need an absolute reference? I've tried but cannot get the syntax right. "Bob Phillips" wrote: This works for me Dim mpFormula As String mpFormula = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]:R[" & _ subrow2 & "]C[" & subcol2 & "])" ActiveCell.FormulaR1C1 = mpFormula -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... Thankyou but I still have the same problem. It compiles but won't run. It comes up with Run-time error 1004 - Application-defined or object-defined error. What I want it to do eg if subrow1=4, subcol1=9, subrow2=10, subcol2=9 and ActiveCell is B4 is to put the formula = sum(i4:i10) in B4. "Bob Phillips" wrote: ActiveCell.FormulaR1C1 = "=SUM(R[" & subrow1 & "]C[" & subcol1 & "]):R[" & subrow2 & "]C[" & subcol2 & "])" -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sue" wrote in message ... I need to enter a sum formula by code. I can establish the coordinates of the range in R1C1 form. Say the points are (subrow1,subcol1) and (subrow2,subcol2). I can get where I want to put the formula. I have tried: ActiveCell.FormulaR1C1="=sum(R[subrow1]C[subcol1]):R[subrow2]C[subcol2]) or ActiveCell.FormulaR1C1="=sum(R[subrow1]C[-1]:R[subrow2]C[-1]) - as the column is adjacent - and variations on the theme but everything with a variable in it produces a run time error. I need the total of a variable amount of the column (which is adjacent to where the total is required.) The formula can not be turned into a fixed offset but must be a range from the two points. Help! -- Dave Peterson |
| All times are GMT +1. The time now is 07:16 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com