Summing array element per column

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
How do you sum column A, B,C as shown below using VBA:

ABCexpected result: ABS(A+B+C)
1-236
23-49
-34512
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
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
 

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
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.
 

millz

New member
Joined
Aug 6, 2013
Messages
32
Reaction score
0
Points
0
Location
Singapore
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 [COLOR=#333333]Total = Total + Abs(Cells(r, c).Value)[/COLOR]

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.
 

nepai

New member
Joined
Feb 19, 2013
Messages
13
Reaction score
0
Points
0
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.
 
Top