Create sum formula when using index column/row references
 

I've got a program that I've created in Excel using VBA. I'm creating some
calculations from sheets that are imported from elsewhere. I'm needing to
create a sum formula on the worksheet. The column and row reference for
where this formula will be placed will differ by worksheet. I'm using some
variables like mCol and mRow to keep track of which column and row I'm doing
stuff on. These variables are tracking the column and row reference by index
number, not by the letter identifier of the column.

Now, I'm at the point where I'm having to place a sum function in a cell,
except that I need the sum function to add up all of the cells above it. So,
for example, if the sum formula goes into C25, I want it to place a formula
that will go into cell C25, something like sum(c1:c24). But I'm using index
numbers instead of column letters to track which column I'm doing this in.
So, mCol = 3 and mRow = 25.

I can't figure out how to construct the sum formula using my index variables.

Create sum formula when using index column/row references
 

One way is to use .formulaR1C1 and you don't have to worry about much:

with worksheets("sheet999")
.cells(mrow,mcol).formular1c1 = "=sum(r1c:r[-1]c)"
end with

R1C means row 1 of the same column.
r[-1]c means the row above in the same column

But if you wanted:

with worksheets("sheet999")
.cells(mrow,mcol).formula _
= "=sum(" & .range(.cells(1,mcol),.cells(mrow-1,mcol)).address & ")"
end with


Stu W wrote:

I've got a program that I've created in Excel using VBA. I'm creating some
calculations from sheets that are imported from elsewhere. I'm needing to
create a sum formula on the worksheet. The column and row reference for
where this formula will be placed will differ by worksheet. I'm using some
variables like mCol and mRow to keep track of which column and row I'm doing
stuff on. These variables are tracking the column and row reference by index
number, not by the letter identifier of the column.

Now, I'm at the point where I'm having to place a sum function in a cell,
except that I need the sum function to add up all of the cells above it. So,
for example, if the sum formula goes into C25, I want it to place a formula
that will go into cell C25, something like sum(c1:c24). But I'm using index
numbers instead of column letters to track which column I'm doing this in.
So, mCol = 3 and mRow = 25.

I can't figure out how to construct the sum formula using my index variables.

--

Dave Peterson

Create sum formula when using index column/row references
 

Thanks, Dave. Worked perfectly. This forum is always a great resource.
Much appreciate the help.


"Dave Peterson" wrote:

One way is to use .formulaR1C1 and you don't have to worry about much:

with worksheets("sheet999")
.cells(mrow,mcol).formular1c1 = "=sum(r1c:r[-1]c)"
end with

R1C means row 1 of the same column.
r[-1]c means the row above in the same column

But if you wanted:

with worksheets("sheet999")
.cells(mrow,mcol).formula _
= "=sum(" & .range(.cells(1,mcol),.cells(mrow-1,mcol)).address & ")"
end with


Stu W wrote:

I've got a program that I've created in Excel using VBA. I'm creating some
calculations from sheets that are imported from elsewhere. I'm needing to
create a sum formula on the worksheet. The column and row reference for
where this formula will be placed will differ by worksheet. I'm using some
variables like mCol and mRow to keep track of which column and row I'm doing
stuff on. These variables are tracking the column and row reference by index
number, not by the letter identifier of the column.

Now, I'm at the point where I'm having to place a sum function in a cell,
except that I need the sum function to add up all of the cells above it. So,
for example, if the sum formula goes into C25, I want it to place a formula
that will go into cell C25, something like sum(c1:c24). But I'm using index
numbers instead of column letters to track which column I'm doing this in.
So, mCol = 3 and mRow = 25.

I can't figure out how to construct the sum formula using my index variables.

--

Dave Peterson