Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Total a row in VB

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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!
    Best regards,

    Martin J.A. Maatman Oonk

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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))"

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 01:22, Tikas A. Planck wrote:
    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))"
    Man you're great!
    How is it you know so much of VB?!?!
    Best regards,

    Martin J.A. Maatman Oonk

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Hengelo
    Posts
    79
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    On 2002-02-22 01:22, Tikas A. Planck wrote:
    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))"
    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.....???
    Best regards,

    Martin J.A. Maatman Oonk

  6. #6

    Join Date
    Feb 2002
    Posts
    47
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •