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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top