VBA to Sum Mulitple Lines of Data to One Line of Data

[dallin01]

Hi, I'm looking for code/examples that sums multiple lines of data in one total line of Data. Example: I would like to sum the amounts by contract into new worksheet one line for each contract. Thankyou!!

Data:
Date Contract Amount
1/1/1 11111 555
1/1/1 11111 666
1/1/1 11111 777
2/2/2 22222 888
3/3/3 33333 999
3/3/3 33333 111

New worksheet data:
date contract Amount
1/1/1 1111 1998
2/2/2 2222 777
3/3/3 3333 1110

 

[kevatarvind]

did you try pivot table ?

pivot table is the best option rather then the VB code

 

[dallin01]

Yes, however, I was hoping for some code examples to expand my vba horizons.

 

[kevatarvind]

ok then try below code assuming your Data in Sheet name DATA

Sub Test()
On Error Resume Next
Sheets("Result").Select
If Err.Number = 9 Then Sheets.Add.Name = "Result"
Sheets("Result").UsedRange.ClearContents
Sheets("DATA").UsedRange.Copy Sheets("Result").Range("A1")
lc = Range("B" & Rows.Count).End(xlUp).Row
Range("D1").Value = "Amount"
Range(Range("D2"), Range("D" & lc)).FormulaR1C1 = "=Sumif(c2:c2,Rc[-2],c3:c3)"
Range(Range("D2"), Range("D" & lc)).Value = Range(Range("D2"), Range("D" & lc)).Value
Columns(3).Delete
ActiveSheet.Range("A:C").RemoveDuplicates Columns:=2, Header:=xlYes
Range("A1").Select
End Sub

 

[JoeMo]

Hi, I'm looking for code/examples that sums multiple lines of data in one total line of Data. Example: I would like to sum the amounts by contract into new worksheet one line for each contract. Thankyou!!

Data:
Date Contract Amount
1/1/1 11111 555
1/1/1 11111 666
1/1/1 11111 777
2/2/2 22222 888
3/3/3 33333 999
3/3/3 33333 111

New worksheet data:
date contract Amount
1/1/1 1111 1998
2/2/2 2222 777
3/3/3 3333 1110

Will your data ever have more than one date for the same contract?

 

[dallin01]

Yes, that is possible

 

[dallin01]

This is good! I'll tweek according to my needs. Thank you very much!

 

[mirabeau]

Yes, however, I was hoping for some code examples to expand my vba horizons.

OK.

Here's another code example.

Change in DATAsheet Column3 automatically adjusts sum in Result sheet.

Sub contracts_sum()
Dim a, b, c, q(), i
Sheets("DATA").Activate
a = Range("B1", Cells(Rows.Count, "b").End(3)(2)).Value
ReDim q(1 To UBound(a, 1))
Cells(1).Resize(, 3).Copy Sheets("Result").Cells(1)
b = 2
For i = 3 To UBound(a, 1)
       If a(i, 1) <> a(b, 1) Then
        c = c + 1
        Cells(b, 1).Resize(, 2).Copy Sheets("Result").Cells(c + 1, 1)
        q(c) = Cells(b, 3).Resize(i - b).Address
        b = i
    End If
Next i
For i = 1 To c
    Sheets("Result").Cells(i + 1, 3) = "=sum(DATA!" & q(i) & ")"
Next i
End Sub

 

[dallin01]

Thanks again for the hints!