Macro to Find, Sum, and Place Values

Fairly simple one here that I can't seem to figure out, I've attached an example workbook.

I just need a Macro (a formula isn't appropriate in this case) that will find the respective assets hours values on the Data worksheet and then sum them into the table on the Table Worksheet.

The correct sums are on the Table Worksheet to cross-check/verify the solution. Thanks in advance.
FindPlaceMacro.xlsx
LVL 1
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Microsoft Excel ExpertCommented:
i dont think you need to overkill it with VBA

just put this formula in H3 of Table Sheet and then Drag down and you get your result.

=SUMIFS(Data!C:C,Data!B:B,Table!G3)
Author Commented:
Hi JimJam... I know this can be accomplished easily with formulas; however, in my real workbook I need separation between several summation methods. Basically and end-user needs to choose their summation method and then "execute" the transfer of values from one place to another.

In my head there are two ways to do this; one is to have the macro place a specific SUMIFS formula in the table columns, or to just copy and paste over the values from the data source.
Microsoft Excel ExpertCommented:
if you really want to do it with VBA then here you go

``````Sub test()

Range("A2").Select
ActiveCell.Offset(1, 1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"
ActiveCell.Select
Selection.Copy
ActiveCell.Offset(0, 2).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
End Sub
``````

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
Works pretty well, couple things:
Can you explain
``````ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"
``````
That's the part that was preventing me from doing this on my own

Is there anything we can do about the "hanging rows" in the second asset group?
If this is running on a large data set, will this be the most efficient code to reduce Calculation Time?
Microsoft Excel ExpertCommented:
so couple of things to remember and also to make this better and improve.

A) i am glad that you are using the Table feature of excel in the table sheet. that is why when the macro puts the formula in first cell, automatically it populates to the last cell.  therefore no matter how big your table is, the formula will just work fine.

B) SUMIF is much better in terms of Speed calculation. of course there are other ways to calculate this to for example SUMPRODUCT and other SUM with IF etc, but SUMIF is much faster than the others, besides I used the whole column reference from the Sheet "Data" if you want to make this faster then i would suggest you use create a Table from your Data sheet, lets assume it is called Table1 then instead of the  ActiveCell.FormulaR1C1 = "=SUMIF(Data!C2,RC[-1],Data!C3)"  you could use ActiveCell.FormulaR1C1 = =SUMIF(Table1[Asset],[@[Asset Group 1]],Table1[Hours])"  which would be much faster in calculation becuase it will not take the whole column but only the data in the table.
Author Commented:
Cool, all good to know for the production workbook, I'll post here if I have a follow up question. Thank You Again!
Microsoft Excel ExpertCommented:
you are most welcome. i am glad i was able to help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.