SOLVED

For Loop to add two columns in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-1768294%22%20slang%3D%22en-US%22%3EFor%20Loop%20to%20add%20two%20columns%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768294%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20use%20For%20loops%20in%20Excel%20VBA%20to%20add%20two%20columns%20value%20and%20update%20the%20result%20in%20to%20another%20column.%20I%20have%20mentioned%20the%20table%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22192%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%20height%3D%2220%22%3EA%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EB%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EC%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E31%3C%2FTD%3E%3CTD%3E37%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E95%3C%2FTD%3E%3CTD%3E13%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E20%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E77%3C%2FTD%3E%3CTD%3E14%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E69%3C%2FTD%3E%3CTD%3E39%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E54%3C%2FTD%3E%3CTD%3E77%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E28%3C%2FTD%3E%3CTD%3E74%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E49%3C%2FTD%3E%3CTD%3E54%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E30%3C%2FTD%3E%3CTD%3E8%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E29%3C%2FTD%3E%3CTD%3E62%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E57%3C%2FTD%3E%3CTD%3E89%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20height%3D%2220%22%3E47%3C%2FTD%3E%3CTD%3E52%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKindly%20also%20help%20me%20to%20understand%20the%20difference%20between%20for%20loops%20and%20do%20while%20loops%2C%20where%20we%20need%20use%20them%20in%20Excel%20VBA.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERoshan%20Kumar%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1768294%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768377%22%20slang%3D%22en-US%22%3ERe%3A%20For%20Loop%20to%20add%20two%20columns%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768377%22%20slang%3D%22en-US%22%3EYou%20mean%20to%20say%20that%2C%20you%20want%20to%20add%20these%20values%20(shown%20above)%20into%202%20new%20columns%3F%20And%20what%20do%20u%20mean%20by%20update%20another%20column%20!%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768397%22%20slang%3D%22en-US%22%3ERe%3A%20For%20Loop%20to%20add%20two%20columns%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768397%22%20slang%3D%22en-US%22%3EThanks%20for%20looking%20into%20it.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20want%20to%20sum%20A%20and%20B%20column%20and%20show%20the%20sum%20in%20C%20column%20for%20all%20cells%20which%20has%20values%20by%20using%20For%20loop%20in%20VBA%20Excel%20as%20I%20am%20learning%20VBA%20Excel%20as%20I%20am%20beginner..%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768420%22%20slang%3D%22en-US%22%3ERe%3A%20For%20Loop%20to%20add%20two%20columns%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768420%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F523429%22%20target%3D%22_blank%22%3E%40Roshan_K%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20say%20for%20loops%20are%20for%20situations%20where%20you%20know%20how%20many%20times%2C%20at%20most%2C%20the%20loop%20should%20iterate%20whereas%20Do%20loops%20are%20for%20instances%20where%20you%20want%20the%20loop%20to%20repeat%20while%20or%20until%20a%20particular%20condition%20is%20reached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20your%20example%20data%20(assuming%20it%20is%20in%20A1%3AB12%20and%20also%20assuming%20your%20worksheet%20is%20the%20active%20worksheet%20as%20I%20did%20not%20fully%20qualify%20my%20ranges%20by%20specifying%20which%20worksheet%2C%20like%20Worksheets(%22Sheet1%22).Range(%22A1%22))%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3ESub%20AddCols1()%0A%20%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20%20%0A%20%20%20%20%20For%20i%20%3D%201%20To%2012%0A%20%20%20%20%20%20%20%20%20%20Range(%22C%22%20%26amp%3B%20i).Value%20%3D%20Range(%22A%22%20%26amp%3B%20i).Value%20%2B%20Range(%22B%22%20%26amp%3B%20i).Value%0A%20%20%20%20%20Next%20i%0A%20%20%20%20%20%0AEnd%20Sub%0A%0ASub%20AddCols2()%0A%20%20%20%20%20Dim%20cell%20As%20Range%0A%20%20%20%20%20%0A%20%20%20%20%20For%20Each%20cell%20In%20Range(%22A1%3AA12%22).Cells%0A%20%20%20%20%20%20%20%20%20%20cell.Offset(0%2C%202).Value%20%3D%20cell.Value%20%2B%20cell.Offset(0%2C%201).Value%0A%20%20%20%20%20Next%20cell%0A%20%20%20%20%20%0AEnd%20Sub%0A%0ASub%20AddCols3()%0A%20%20%20%20%20Dim%20i%20As%20Long%0A%20%20%20%20%20%0A%20%20%20%20%20i%20%3D%201%0A%20%20%20%20%20%0A%20%20%20%20%20Do%20While%20Range(%22A%22%20%26amp%3B%20i).Value%20%26lt%3B%26gt%3B%20%22%22%0A%20%20%20%20%20%20%20%20%20%20Range(%22C%22%20%26amp%3B%20i).Value%20%3D%20Range(%22A%22%20%26amp%3B%20i).Value%20%2B%20Range(%22B%22%20%26amp%3B%20i).Value%0A%20%20%20%20%20%20%20%20%20%20i%20%3D%20i%20%2B%201%0A%20%20%20%20%20Loop%0A%20%20%20%20%20%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1768643%22%20slang%3D%22en-US%22%3ERe%3A%20For%20Loop%20to%20add%20two%20columns%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1768643%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675152%22%20target%3D%22_blank%22%3E%40JMB17%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20so%20much%2C%20Its%20very%20helpful%20and%20working%20fine.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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:

 

ABC
3137 
9513 
2014 
7714 
6939 
5477 
2874 
4954 
308 
2962 
5789 
4752 

 

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

 

 

7 Replies
Highlighted
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 !
Highlighted
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

Highlighted
Best Response confirmed by Roshan_K (Occasional Contributor)
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

 

Highlighted

@JMB17 

 

Thanks so much, Its very helpful and working fine.

 

 

Highlighted

@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!

 

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

@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.