CODEWITHSUNDEEP
vbaexcelexcel-2010
angular2neewbie
angular2neewbie

Reputation: 129

SUM formula vba code

How is it possible to sum two columns in an excel file into another column. I want to sum Column A with Column B and the result should be shown in column C, but how can I do it in vba?

For example:

Col A.

  1. 1
  2. 2
  3. 3

Col B.

  1. 1
  2. 2
  3. List item

Col C.

  1. 2
  2. 4
  3. 6

Upvotes: 0

Views: 1961

Answers (4)

Karthick Gunasekaran
Karthick Gunasekaran

Reputation: 2713

As Gary's Student said, there are many ways, one is below if

A1 contains 1 and B1 contains 1 A2 contains 2 and B2 contains 2 A3 contains 3 and B3 contains 3

in C1 Column paste the below formula

 =IF(OR(ISBLANK(A1),ISBLANK(B1)),"Either A or B is Blank",SUM(A1:B1))

and select the column C1 to C3 and press Ctrl + D

in VBA,

Sub Sample()
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 1 To lastrow
If Cells(i, 1) = "" And Cells(i, 2) = "" Then
Cells(i, 3) = "Both are Blank"
ElseIf Cells(i, 1) <> "" And Cells(i, 2) = "" Then
Cells(i, 3) = "B is Blank"
ElseIf Cells(i, 1) = "" And Cells(i, 2) <> "" Then
Cells(i, 3) = "A is Blank"
ElseIf Cells(i, 1) <> "" And Cells(i, 2) <> "" Then
Cells(i, 3) = Cells(i, 1) + Cells(i, 2)
End If
Next i
End Sub

or you can use Function. Directly into excel sheet like formula Simply type sumformula(A1,B1) in C1, results will appears in C1 row after enter.

please see below

Function sumformula(a As String, b As String)
If a = "" And b = "" Then
    sumformula = "Both are Blank"
ElseIf a <> "" And b = "" Then
    sumformula = "B is Blank"
ElseIf a = "" And b <> "" Then
    sumformula = "A is Blank"
ElseIf a <> "" And b <> "" And IsNumeric(a) = False And IsNumeric(b) = True Then
    sumformula = "Column A is not a number"
ElseIf a <> "" And b <> "" And IsNumeric(a) = True And IsNumeric(b) = False Then
    sumformula = "Column B is not a number"
ElseIf a <> "" And b <> "" And IsNumeric(a) = True And IsNumeric(b) = True Then
    sumformula = WorksheetFunction.Sum(a, b)
End If
End Function

Hope it helps

Upvotes: 0

Rob
Rob

Reputation: 65

You're not posing the problem very well ... if you're interested in adding values in a column (like you) wrote, I don't see any reason why you would want to use VBA at all. Just add the values in the cells directly.

Apart from that, you can always use a function like

Function DoColumnStuff( column_1 as Range, column_2 as Range ) as Variant

  Dim returnColumn() as Variant

  ' resize returnColumn to a sensible size and
  ' populate it with sensible values, derived out of
  ' column_1 and column_2

  DoColumnStuff = returnColumn
End Function

So, you could do with your arguments whatever your heart desires and return that as a column. Two things to note: Firstly, if you want the function to return a column, you will have to resize returnColumn to a 2-dimensional array with 1 column dimension (e.g. 10 rows, 1 column ~~> use Redim returnColumn( 1 to 10, 1 to 1 ) for resizing). Secondly, you need to insert the function as a Matrix-Function in Excel, i.e. select the whole output range, type in the formula and press Shift-Enter

Upvotes: 0

Avishay Cohen
Avishay Cohen

Reputation: 2228

if that's all you need, why not just use the excel formula for cell C1 "=SUM(A1,B1)"? if it's part of a more complex vba macro you can use

cells(1,3).Formula = "=SUM(A1,B1)"
Range("C1:C3").FillDown

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96791

There many ways. For example:

Sub AddColumns()
    Dim i As Long
    For i = 1 To 3
        Cells(i, 3).Value = Cells(i, 1).Value + Cells(i, 2).Value
    Next i
End Sub

You could also use Evaluate() which is slightly faster and slightly more obscure.

Upvotes: 0

Related Questions