Results 1 to 5 of 5

Thread: Summing array element per column

  1. #1

    Summing array element per column



    Register for a FREE account, and/
    or Log in to avoid these ads!

    How do you sum column A, B,C as shown below using VBA:

    A B C expected result: ABS(A+B+C)
    1 -2 3 6
    2 3 -4 9
    -3 4 5 12

  2. #2
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    You can try this:
    Code:
    Sub test3()
        For r = 1 To 3
            Total = 0
            For c = 1 To 3
                Total = Total + Abs(Cells(r, c).Value)
            Next
            Cells(r, c) = Total
        Next
    End Sub

  3. #3
    Thanks for the reply. That one works!
    I'm trying to read the code I understand it a little.
    If I put the data starting at row 2, I just I have to changed the r to start at 2. Same goes with the column.
    But what if I only want to abs(A+C) or if I add column D, that is abs(A+B+D).
    This is the logic that I'm trying to do actually.

  4. #4
    Acolyte millz's Avatar
    Join Date
    Aug 2013
    Location
    Singapore
    Posts
    32
    Articles
    0
    if your data starts at row 2, change the line For r = 1 to 3 to For r = 2 to 4 which I think you figured that out.
    if you only want to add column A and C, you need to add an If condition.
    Code:
    If c <> 2 Then Total = Total + Abs(Cells(r, c).Value)

    This means if the Column number is not equals to 2 (B), then do the math.

    Code:
            For c = 1 To 3
                Total = Total + Abs(Cells(r, c).Value)
            Next
    and because this code is actually telling the computer to add/increase c each time it loops, starting from 1, and up till 3, after the third loop, c is increased to 4. That makes the condition of 1 to 3 fails, and then stop looping, however the variable c is still holding the value 4.

    And in the line after that, Cells(r, c) = Total is actually printing the Total in column D, because c equals 4.

  5. #5
    Got it!
    To skip column 2 & 3, I just add "And" in the condition:
    Code:
     If c <> 2 And c <> 3
    And to place the result in a selected column I just add a number in Cells(r, c), say Cells(r,c+1). This will move the result one column to the right.
    I'm learning! Thanks millz!
    Now I'm gonna figure out how to use Range instead of Cells.

Posting Permissions

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