'I click the caculate button and how to sum E4 till End of records. Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) End Sub

____________________________
Telecommunication programmer.
Sip, Isdn - technology.

____________________________
Attached:

this is something i want to do (manualy ) but where E5 can not be fixed (for automatic) .
For Each cl In Range("E4", "E5").Cells
and
Sheets("Sheet1").Range("E6").Value = TempSum
it has to be not E6 it has to be E(end of original records counted).

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)     Dim cl As Range, TempSum As Double         ' Application.Volatile ' this is optional         TempSum = 0                  On Error Resume Next ' ignore cells without values                  For Each cl In Range("E4", "E5").Cells                 TempSum = TempSum + cl.Value         Next cl                  Set cl = Nothing         Sheets("Sheet1").Range("E6").Value = TempSum          End Sub

[Edited by jonybd on 19-06-2005 at 07:01 AM GMT]

____________________________
Telecommunication programmer.
Sip, Isdn - technology.

Here a quick function I did for you.   Function FloatSum(XRow As Integer, YClm As Integer)     Dim CurrRow As Integer     Dim TmpHolder As Integer          CurrRow = XRow     TmpHolder = 0     Do While ((IsNumeric(Sheet1.Cells(CurrRow, YClm).Value)) And (Trim(Sheet1.Cells(CurrRow, YClm).Value) <> ""))         TmpHolder = TmpHolder + CInt(Sheet1.Cells(CurrRow, YClm).Value)         CurrRow = CurrRow + 1     Loop          FloatSum = TmpHolder      End Function

I think that this is what you wanted. To use the function, just give it the stating cell coordinated.

EX : D5 = (5,4)

The function will return the sum of all the number following the cell until it meet with a non numeric value or a empty cell.

Daniel B.

____________________________
A Stick give a wise man something to think about... and a fool, something to put in is mouth.

You could also use UsedRange which will keep going regardless of any empty cells...

For cntRow = 4 To ActiveSheet.UsedRange.Row - 1 + ActiveSheet.UsedRange.Rows.Count myTotal = myTotal + ActiveSheet.Cells(cntRow,5).Value Next cntRow

____________________________
Eggheads unite! You have nothing to lose but your yolks.

how can you apply the function into VB , i have the same problem but when i copy and paste your solution in VB it doesnot work.

Function FloatSum(XRow As Integer, YClm As Integer)
    Dim CurrRow As Integer
    Dim TmpHolder As Integer
    
    CurrRow = XRow
    TmpHolder = 0
    Do While ((IsNumeric(Sheet1.Cells(CurrRow, YClm).Value)) And (Trim(Sheet1.Cells(CurrRow, YClm).Value) <> ""))
        TmpHolder = TmpHolder + CInt(Sheet1.Cells(CurrRow, YClm).Value)
        CurrRow = CurrRow + 1
    Loop
    
    FloatSum = TmpHolder
    
End Function

** with the example at the top, how can i substitue the rows and columns in your VB program?

help from a VB beginner

Hi, try this......................

Sub sumrow()

Dim c As Long
Dim sm As Double
sm = 0
For c = 1 To Cells.count
    If Sheets("Test").Cells(c, 1).Value = "" Then
        Sheets("Test").Cells(c, 1).Value = sm
        Exit For
    Else
       sm = sm + Sheets("Test").Cells(c, 1).Value
    End If

Next c

End Sub