Total a row in VB

[MJA]

Hello once more,

I have a serie of numbers in A1, B1, C1 to x1.
I want to total the numbers from A1 to x1, the answer must stand in the cell next to x1!!!

[A1].End(xlToRight)(1, 2).Value = Application.Sum(Range([A1], [A!].End(xlToRight)))

This does the trick, BUT when I change a number the total doesn’t change.
I must play the macro again and then it will.
But I only want the macro to play just once.
Therefore it must be a formula that stands in x1.
How do I do that?
The solution must be in VB!

[Mudface]

The following might do the trick, it will total the values in the first row from A1 to X1 and put the result in Y1: -

Private Sub Worksheet_Change(ByVal Target As Excel.Range)

If Target.Count > 1 Then Exit Sub
If Target.Row <> 1 Then Exit Sub
Cells(1, 25).Formula = "=SUM(A1:X1)"

End Sub

[Tikas A. Planck]

On 2002-02-22 00:34, MJA wrote:
Hello once more,

I have a serie of numbers in A1, B1, C1 to x1.
I want to total the numbers from A1 to x1, the answer must stand in the cell next to x1!!!

[A1].End(xlToRight)(1, 2).Value = Application.Sum(Range([A1], [A!].End(xlToRight)))

This does the trick, BUT when I change a number the total doesn’t change.
I must play the macro again and then it will.
But I only want the macro to play just once.
Therefore it must be a formula that stands in x1.
How do I do that?
The solution must be in VB!

Dim cell As Range
Set cell = [A1].End(xlToRight)(1, 2)
cell.Formula = "=SUM(A1:OFFSET(" & cell.Address(False, False) & ",0,-1))"

[MJA]

On 2002-02-22 01:22, Tikas A. Planck wrote:

Dim cell As Range
Set cell = [A1].End(xlToRight)(1, 2)
cell.Formula = "=SUM(A1:OFFSET(" & cell.Address(False, False) & ",0,-1))"

Man you're great!
How is it you know so much of VB?!?!

[MJA]

On 2002-02-22 01:22, Tikas A. Planck wrote:

Dim cell As Range
Set cell = [A1].End(xlToRight)(1, 2)
cell.Formula = "=SUM(A1:OFFSET(" & cell.Address(False, False) & ",0,-1))"

If I want to do exactly the same but instead of rows I have columns. E.g. numbers in A1:Ax, I want the total in the cell under Ax.
Then what code do I get.....???

[Tikas A. Planck]

Dim cell As Range
Set cell = [A1].End(xlDown)(2, 1)
cell.Formula = "=SUM(A1:OFFSET(" & cell.Address(False, False) & ",-1,0))"