SUM Numbers Above

[Vampy99]

Hi,

I've wrote some VB code that displays a variation of numbers dependent on what is entered. The amount of rows there will be will differ because of this.

My question is simply does anyone know a formula or any code that SUMS everything above it? e.g

=SUM(A2:AX)

or perhaps

=SUM(A2:A"one_above_me")

A2 is constant. AX is the cell above where the formula is.

Hope this makes sense.

Luke

 

[Oaktree]

=sum(indirect("a2:a"&row()-1))

 

[Vampy99]

Works a treat, thank you very much

Luke

 

[Vampy99]

Oops... this works great in Excel, but I cannot use this in VB due to their being 4 sets of quotation marks.

I have the following line of code:

Range(cellrefb & total).Formula = "=Sum(indirect("b2:b"& Row() - 1))"

cellrefb and total are both variables to put the formula in the correct cell.

Any ideas as to how to use this in VB, getting around the quotation marks problem?

Thanks,

Luke

 

[T. Valko]

Oops... this works great in Excel, but I cannot use this in VB due to their being 4 sets of quotation marks.

I have the following line of code:


cellrefb and total are both variables to put the formula in the correct cell.

Any ideas as to how to use this in VB, getting around the quotation marks problem?

Thanks,

Luke

You need double quotes like this:

Range(cellrefb & total).Formula = "=Sum(indirect(""b2:b""& Row() - 1))"

 

[Oaktree]

I usually prefer to parse it out with either CHR(34) or """" (four double quotes).

Range(cellrefb & total).Formula = "=Sum(indirect(" & Chr(34) & "B2:B" & Chr(34) & "&Row()-1))"

 

[Vampy99]

Thank you T. Valko, that gives me exactly what I need.

I'm still relatively new to VB, so the tips you have given me will hold me in good stead. Thanks again T.Valko & Oaktree,

Luke

 

[shg]

Or

Cells(total, cellrefb).FormulaR1C1 = "=sum(r2c:r[-1]c)"

... which is non-volatile.

Or you can select A2, and do Insert > Name > Define, relN (as in "relative north") refers to =A1 (no $ signs), then use the formula

Cells(total, cellrefb).FormulaR1C1 = "=sum(r2c:relN)"

... which ALWAYS refers to the cell above, even if you insert rows.

 

[T. Valko]

Thank you T. Valko, that gives me exactly what I need.

I'm still relatively new to VB, so the tips you have given me will hold me in good stead. Thanks again T.Valko & Oaktree,

Luke

You're welcome. Thanks for the feedback!