VBA Sum to colombs

duncan4000

New Member
Joined
Nov 4, 2011
Messages
4
im trying to write a program that reads the values in 2 columns and sums them together and puts the results in the 1st column but i cant get it to work
this is what i have so far. thanks for any help


Sub sumvalues()
Dim Column1, Column2
Dim StartRow, EndRow
Dim TempVal As Integer
Dim TempVal2 As Integer
Dim Ans As Integer
Dim RowNdx
Column1 = InputBox( _
prompt:="Input 1st column Letter")

Column2 = InputBox( _
prompt:="Input 2st column letter")

StartRow = InputBox( _
prompt:="Input start row number")

EndRow = InputBox( _
prompt:="Input end row number")

RowNdx = StartRow

Do
TempVal1 = Cells(RowNdx, "Column1").Value
TempVal2 = Cells(RowNdx, "Column2").Value
Ans = TempVal1 + TempVal2
Cells(RowNdx, "Column2").ClearContents
Cells(RowNdx, "Column1").ClearContents
Cells(RowNdx, "Column1").Value = Ans


RowNdx = RowNdx + 1
Loop Until (EndRow + 1)

End Sub
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
Im trying to write a program that reads the values in 2 columns and sums them together and puts the results in the 1st column but i cant get it to work
this is what i have so far. thanks for any help

Let me see if I understand: you are going to overwrite the results in the first column? If you have
A B

2 6
4 1
8 2

the result will be

A B

8 6
5 1
10 2

Is this your goal?
 

Worf

Well-known Member
Joined
Oct 30, 2011
Messages
4,093
See if this works for you:

Code:
Sub sumvalues()
Dim Column1, Column2, i%, sri%, eri%
Dim StartRow, EndRow, esum&

Column1 = InputBox( _
prompt:="Input 1st column Letter")

Column2 = InputBox( _
prompt:="Input 2st column letter")

StartRow = InputBox( _
prompt:="Input start row number")

EndRow = InputBox( _
prompt:="Input end row number")

sri = CVar(StartRow)    ' converts string to integer
eri = CVar(EndRow)

For i = sri To eri
    esum = Range(Column1 & CStr(i)).Value + Range(Column2 & CStr(i)).Value
    Range(Column1 & i).FormulaR1C1 = esum
    Range(Column2 & i).FormulaR1C1 = " "
Next
End Sub
 

duncan4000

New Member
Joined
Nov 4, 2011
Messages
4

ADVERTISEMENT

works great thanks. one more question if a row is blank it comes up with an error anyway to make it just skip that row instead
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
A few comments on the suggested code:

1. As a general good practice, I would recommend declaring variables that are going to be used as a particular type, as that type, rather than as Variants.

2. If you are putting a value, rather than a formula, in a cell then use .Value rather than .FormulaR1C1 or .Formula

3. Putting a space character in a cell often leads to problems later. A null string "" would be better, but better still would be to use .ClearContents

My code for you to test (in a copy of your workbook) would be:

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> sumvalues_v2()<br>    <SPAN style="color:#00007F">Dim</SPAN> Column1 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Column2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> StartRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, EndRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, rws <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Col1, Col2<br>    <br>    Column1 = Application.InputBox( _<br>        Prompt:="Input 1st column Letter", Type:=2)<br>    Column2 = Application.InputBox( _<br>        Prompt:="Input 2nd column Letter", Type:=2)<br>    StartRow = Application.InputBox( _<br>        Prompt:="Input start row number", Type:=1)<br>    EndRow = Application.InputBox( _<br>        Prompt:="Input end row number", Type:=1)<br>    rws = EndRow - StartRow + 1<br>    Col1 = Cells(StartRow, Column1).Resize(rws).Value<br>    Col2 = Cells(StartRow, Column2).Resize(rws).Value<br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> rws<br>        Col1(i, 1) = Col1(i, 1) + Col2(i, 1)<br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Cells(StartRow, Column1).Resize(rws).Value = Col1<br>    Cells(StartRow, Column2).Resize(rws).ClearContents<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

If this still causes an error, report just what the error is and click Debug to determine the offending line of code.

If the problem is in this line ..
Code:
Col1(i, 1) = Col1(i, 1) + Col2(i, 1)
.. then when Debugging, hover over i, Col1(i,1) and Col2(i,1) and report those 3 values.
 

duncan4000

New Member
Joined
Nov 4, 2011
Messages
4
works great thanks, just one more question if the sum= 0 is there a way to leave the cell blank instead of putting a 0 there? thanks for all the help
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
works great thanks, just one more question if the sum= 0 is there a way to leave the cell blank instead of putting a 0 there? thanks for all the help
Try adding in the blue line of code where shown.
Rich (BB code):
For i = 1 To rws
    Col1(i, 1) = Col1(i, 1) + Col2(i, 1)
    If Col1(i, 1) = 0 Then Col1(i, 1) = ""
Next i
 

Watch MrExcel Video

Forum statistics

Threads
1,122,491
Messages
5,596,460
Members
414,069
Latest member
StudExcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top