Hi all,

I am trying to create a macro that will sum all the values in column H
and will put the result in Column I. The macro should only use a range
with cells with values and stop if a blanco cell is found. Something
like the Sum(Above) function that's builtin in Word.
It shouldn't be necessary for the user to have the cursor in the
correct colum (H) but only in the correct row. The idea is that the
code looks for the values in column H starting from the row above the
activecell until a blanc cell is found.

I was able to create some code that does that but the problem is the
..End property doesn't work correctly cause column H has formulas in
each cell therefor causing my macro to always create a formula
=SUM($H$1:$H$17)
instead of
=SOM($H$4:$H$17)
cause H1 to H3 contain a formula but no value


This is the code I've used:
----------------------------------------------------------------------------------------------------------------------
Dim sAddress As String
Dim oRange As Range
Dim oSumRange As Range
Dim lColumn As Long
Dim lRow As Long

lRow = ActiveCell.Row
lColumn = ActiveCell.Column
If lColumn < 8 Then
Set oRange = ActiveSheet.Cells(lRow, 8)
Else
Set oRange = ActiveCell
End If
Set oRange = oRange.End(xlUp).End(xlUp)
sAddress = oRange.Address

Set oSumRange = ActiveSheet.Cells(lRow, 9)
oSumRange.Formula = "=SUM(" & sAddress & ":" & oRange(lRow,
1).Address & ")"

Set oSumRange = Nothing
Set oRange = Nothing
----------------------------------------------------------------------------------------------------------------------

Can somebody please help me to get the result that I need?

TIA
Renate