For Loop to add two columns in Excel

[Roshan_K]

Hi,

I want to use For loops in Excel VBA to add two columns value and update the result in to another column. I have mentioned the table below:

A	B	C
31	37
95	13
20	14
77	14
69	39
54	77
28	74
49	54
30	8
29	62
57	89
47	52

Kindly also help me to understand the difference between for loops and do while loops, where we need use them in Excel VBA.

Thanks

Roshan Kumar

[#2]

You mean to say that, you want to add these values (shown above) into 2 new columns? And what do u mean by update another column !

[#3]

Thanks for looking into it.

I want to sum A and B column and show the sum in C column for all cells which has values by using For loop in VBA Excel as I am learning VBA Excel as I am beginner..

Thanks

[#4 · Accepted solution]

@Roshan_K 

I would say for loops are for situations where you know how many times, at most, the loop should iterate whereas Do loops are for instances where you want the loop to repeat while or until a particular condition is reached.

Using your example data (assuming it is in A1:B12 and also assuming your worksheet is the active worksheet as I did not fully qualify my ranges by specifying which worksheet, like Worksheets("Sheet1").Range("A1")):

Sub AddCols1()
     Dim i As Long
     
     For i = 1 To 12
          Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value
     Next i
     
End Sub

Sub AddCols2()
     Dim cell As Range
     
     For Each cell In Range("A1:A12").Cells
          cell.Offset(0, 2).Value = cell.Value + cell.Offset(0, 1).Value
     Next cell
     
End Sub

Sub AddCols3()
     Dim i As Long
     
     i = 1
     
     Do While Range("A" & i).Value <> ""
          Range("C" & i).Value = Range("A" & i).Value + Range("B" & i).Value
          i = i + 1
     Loop
     
End Sub

[#5]

@JMB17 

Thanks so much, Its very helpful and working fine.

[#6]

@JMB17 

what If I want to subtract the same columns instead of adding, I am now able to achieve the result by using cell function inside the for loop statement given below:

Sub substract()
Dim i As Integer

For i = 1 To 12
Cells(i, 3).Value = Cells(i, 1).Value - Cells(i, 2).Value
Next i

End Sub

I also tried to edit the code which you provided below, where I changed the (+) sign with (-) its not working, will you help me with that please where I need to ammend the code to get it corrected.

Sub subsCols1()
Dim i As Long

For i = 1 To 12
Range("C" & i).Value = Range("A" & i).Value - Range("B" & i).Value
Next i
End Sub

Thanks in advance!

[#7]

It should work. I couldn't say why it isn't without seeing the workbook, though.

[#8]

@Roshan_K 

You said "I also tried to edit the code which you provided below, where I changed the (+) sign with (-) its not working" which really doesn't help us much. In what way did it not work? Is it because you got negative values instead of positive ones (or vice versa)? If so, switch the cell references...

Cells(i, 3).Value = Cells(i, 2).Value - Cells(i, 1).Value

If that is not the problem, then you have to give us more description.