Creating Excel SSheet in Access - How to Paste Formula into Cell

[Mike Thomas]

Using Access and Excell 2003, I am making and populating a spreadsheet from
an Access VB module. The numbers have been placed into contigious cells
(say B2...E2) , now I want to insert into column F2 a formula to sum these
values, eg:

=SUM(B2:E2)

However, my column and row address values are in integer variables, eg
intBegCol, intRow : intEndCol, intRow

where int Row = 2, intBegCol = 2 (eg col B), and intEndCol = 5 (eg col E)

I've been using this code to insert simple values:

objWKS.Cells(intRow, 2).Value = Nz(r!SumOfCourier, 0)

What would be the correct code to use to insert the formula above?

Many thanks
Mike Thomas

 

[Guest]

Using Access and Excell 2003, I am making and populating a spreadsheet from
an Access VB module. The numbers have been placed into contigious cells
(say B2...E2) , now I want to insert into column F2 a formula to sum these
values, eg:

=SUM(B2:E2)

However, my column and row address values are in integer variables, eg
intBegCol, intRow : intEndCol, intRow

where int Row = 2, intBegCol = 2 (eg col B), and intEndCol = 5 (eg col E)

I've been using this code to insert simple values:

objWKS.Cells(intRow, 2).Value = Nz(r!SumOfCourier, 0)

What would be the correct code to use to insert the formula above?

Many thanks
Mike Thomas

Hi Mike, this sort of question is best posted to the Excel community.
However, I'll do my best...

There are a couple properties of a cell that you can choose from:
cell.formula and cell.formular1c1

in cell.formula you insert a formula such as:

cell.formula="=sum(B2:B12)"

you should you the alternate:

cell.formula="=sum(R2C:R[-1]C)"

in this notation R2 is equivalent to to R$2. An absolute reference to row 2.
the C is equivalent to same column as activecell/cell reference.

R[-1]C means one row above the activecell/cell reference in the same column.

So, hopefully you will be able to constuct a string using formular1c1
notation.

Luck
Jonathan

 

[Guest]

objWKS.Cells(intRow, 2).Formula = "=Nz(r!SumOfCourier, 0)"