code to sum values in Excel

[Dan15]

Situation in Excel: I need to the code, that when executed, tells a cell to sum the values in the cells above. The range going up is determined by the value of the cells - as in it keeps totaling the cells going up, until, for example, the value is 2, so that will define the upper limit of the range.

Thanks-

-Dan

 

[RobBroekhuis]

Dan - An example would help
Rob

 

[Zathras]

Is this what you are looking for? You need to add code to test for limits in case the LastValue (e.g. 2) isn't in the list, unless you are absolutely sure that it allways will be. Also, based on your sample data (i.e. 2) I have assumed to use integers. If your data is otherwise, you need to change the definitions.

Option Explicit

Sub test()
  SumOfAbove ActiveSheet.Cells(6, 3), 2
End Sub

Sub SumOfAbove(Target As Range, LastValue As Integer)
Dim nSum As Integer
Dim nRowOffset As Long
  nSum = 0
  nRowOffset = -1
  While Target.Offset(nRowOffset, 0).Value <> LastValue
    nSum = nSum + Target.Offset(nRowOffset, 0)
    nRowOffset = nRowOffset - 1
  Wend
  Target.Value = nSum + LastValue
End Sub

 

[RobBroekhuis]

The way I read Dan's request, he wants a formula rather than a value in the cell ("tells a cell to sum&quot. That's why an example would be useful
Rob

 

[Dan15]

Here is the example:

column 1 column 2 column3
150
Water 2
100
50
50
150
Total

Now, the total line is not part of the template, but is entered with a button. When the button is activated, "total" is entered in column 1 and column 2 is the sum of the cells above. The sum should add up the numbers 100, 50, 50, 150. Column 3, with the number 2, determines when to stop adding more cells to the total.

Zathras, I am trying your solution, but I am coming up with some errors.

Thank you for your help-

-Dan

 

[Zathras]

Try this variation:

Option Explicit

Sub test()
  SumOfAbove ActiveSheet.Cells(6, 3)
End Sub

Sub SumOfAbove(Target As Range)
Dim nSum As Integer
Dim nRowOffset As Long
  nSum = 0
  nRowOffset = -1
  While Target.Offset(nRowOffset, 1).Value = ""
    nSum = nSum + Target.Offset(nRowOffset, 0)
    nRowOffset = nRowOffset - 1
  Wend
  Target.Value = nSum
End Sub