continous calculation

[4808]

Hello all

I am a novice to VBA, I am trying to create a button that any time it is clicked a new row will be inserted below the last entry row and when figures are entered in that row a subtotal will be automatically in the row below continously for as long as rows are inserted and figures are entered.

I hope this is clear enough.

Many thaks.

 

[Chance1234]

what code have you got so far ?

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[4808]

I know how to insert the button on the worksheet thats all.

 

[Chance1234]

ok,

Try recording some macros for the actions you want to do above.

OPen up VBA Editor and take a look at the code, the macros produce.



Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[4808]

I cannot getthe figures to add, this is what i can make sense of so far, below:

Accounts.Activate
Range("A1").Select
Selection.EntireRow.insert

 

[Chance1234]

click on the range keyword ,and hit F1 to bring up help on the range object. Also look up Cell and Row and Columns


To give you a push in the right direction, I have on my spreadsheet in Cells A1:A5 , the numbers 1 2 3 4 5

Sub Push()

    Sheet1.Range("A1:A5").Select
    
    MsgBox Sheet1.Range("A1").Value
    MsgBox Sheet1.Range("A1").Value + Range("A2").Value
    
    For Each Cell In Range("A1:A5")
        MsgBox Cell.Value
    Next
    
    
End Sub

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[Chance1234]

POETS day so bit of code to get you a bit further

Sub Push()
Dim MyTotal As Long
Dim rowCount As Long
Dim intLoop As Long
Dim mycell As Range

rowCount = Sheet1.Rows.Count

For intLoop = 1 To rowCount - 1
    If Sheet1.Cells(intLoop, 1) = "" Then
        Exit For
    End If
Next

rowCount = intLoop
MsgBox rowCount

    For Each mycell In Sheet1.Range(Cells(1, 1), Cells(intLoop, 1))
        MyTotal = MyTotal + mycell.Value
    Next

MsgBox MyTotal
    
    Sheet1.Rows(rowCount).Insert Shift:=xlDown
    Sheet1.Cells(rowCount + 1, 1).Value = MyTotal
    
End Sub

Chance,

Filmmaker, taken gentleman and He tan e epi tas

 

[4808]

Thanx Chance, thats a start.