Need another vba copy cell value to another cell with sum

[richter_2100]

Hi Guys - I need another help to create a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">vba</acronym> to copy data from a cell to another cell. This will be big help for me automating my data.
In example below, We just want to take the sum of the location "TBD" and "TBI" and copy it below the last row. Thanks!

 

[onlyadrafter]

Hello,

code not required (I don't think)

in cell D16 enter the following formula

=SUMPRODUCT(--($A2:$A15=$A16)*(--($B2:$B15=$B16)*(--(LEFT($C2:$C15,2)="TB"))*(D2:D15)))

and copy down and across.

You may also want to look into Pivot Tables.

 

[richter_2100]

Thanks Drafter - I need to auto populate also the column A,B and C.

The solution works but not to my requirement.

 

[onlyadrafter]

Hello,

not very elegant, but gives the results as per your example, and can be used for any amount of rows in the spreadsheet (tested it by copying your sample data).

 Sub SUM_UP_TB()
    MY_LAST_ROW = ActiveSheet.UsedRange.Rows.Count
    For MY_ROWS = 2 To MY_LAST_ROW
        If Left(Range("C" & MY_ROWS).Value, 2) = "TB" Then
            For MY_CHECK = MY_LAST_ROW + 1 To ActiveSheet.UsedRange.Rows.Count + 2
                MY_EXISTING = Range("A" & MY_CHECK).Value & Range("B" & MY_CHECK).Value
                If Range("A" & MY_ROWS).Value & Range("B" & MY_ROWS).Value = MY_EXISTING Then
                    GoTo CONT
                End If
            Next MY_CHECK
                    Range("A" & ActiveSheet.UsedRange.Rows.Count + 1).Value = Range("A" & MY_ROWS).Value
                    Range("B" & ActiveSheet.UsedRange.Rows.Count).Value = Range("B" & MY_ROWS).Value
                    Range("C" & ActiveSheet.UsedRange.Rows.Count).Value = "TBDI"
                    GoTo CONT
CONT:
        End If
    Next MY_ROWS
    Range("D" & MY_LAST_ROW + 1).FormulaR1C1 = _
        "=SUMPRODUCT(--(R2C1:R" & MY_LAST_ROW & "C1=RC1)*(R2C2:R" & MY_LAST_ROW & "C2=RC2)*(LEFT(R2C3:R" & MY_LAST_ROW & "C3,2)=""TB"")*(R2C:R" & MY_LAST_ROW & "C))"
    Range("D" & MY_LAST_ROW + 1).Copy Range("D" & MY_LAST_ROW + 1 & ":G" & ActiveSheet.UsedRange.Rows.Count)
End Sub

 

[richter_2100]

You nailed it my friend!

Thanks a lot!