0

I have the following Problem in VBA:

Consider the following:

Excel table

I want to build the sum of every kind. Go through the column B and gather the amount in column A by the kind and after finishing the loop write the sum in front of every kind in column E. How can I do it?

Thanks

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
maniA
  • 1,437
  • 2
  • 21
  • 42
  • 2
    `=SUMIF($B:$B,$D2,$A$A)`. No vba needed – Scott Craner Dec 07 '15 at 17:30
  • exactly but I have to use VBA because this sheet is created by VBA every time. However if it is not possible else where I will use record macro and your suggestion. Thank you – maniA Dec 07 '15 at 17:32
  • 1
    Why is it not possible to use formulae? In VBA you can easily set a formula for a cell or cells. – Taelsin Dec 07 '15 at 17:33
  • 3
    `Range("E4").formula="= SUMIF($B:$B,$D2,$A$A)"` and then `Range("E4:E9).Autofill` – Scott Craner Dec 07 '15 at 17:34
  • @Taelsin, How do you mean? – maniA Dec 07 '15 at 17:34
  • 2
    @maniA Exactly what Scott just commented :) – Taelsin Dec 07 '15 at 17:35
  • Ok nice I will try it I did not know :) – maniA Dec 07 '15 at 17:37
  • 3
    just a note... it may be worth defining your last row in the formula, as summing the column can be very burdensome in larger worksheets, especially if it's not needed. For example `=SUMIF($B$1:$B$14,$D2,$A$1:$A$14)` ... Also note a typo in @ScottCraner's original formula ... `$A$A` should be `$A:$A`. – Scott Holtzman Dec 07 '15 at 17:40
  • 1
    @ScottHoltzman, nice but another question, how can I do it dynamically? it means I have every time diff number of rows not always 14!? – maniA Dec 07 '15 at 17:43
  • 2
    You will need to either use a advanced filter or copy and paste the whole list then remove duplicates. At this point try on your own and when you have a specific problem come back ask a new question and post the code with the problem. Edit: Usually questions that ask to "Code this for me" get down voted and closed here. You need to show some effort. – Scott Craner Dec 07 '15 at 17:48
  • 3
    @maniA - [Dynamically Find Last of Row of Data](http://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba) ... A little sleuthing goes **a long** way, my friend :) – Scott Holtzman Dec 07 '15 at 17:50
  • 1
    @ScottHoltzman, I have it thanks a lot to all – maniA Dec 07 '15 at 17:58
  • 2
    What about just using a Pivot Table? And if you need it in VBA, use the macro recorder while you do it. – Ron Rosenfeld Dec 07 '15 at 20:39
  • @RonRosenfeld, oh thanks that is exactly where I have no idea could you please give me a reference I saw that one friend used it but I did not understand – maniA Dec 07 '15 at 20:46
  • 1
    I would start with HELP in Excel for Pivot Tables. But the kind of result you are looking for is exactly what they are designed to do. If you enter, in Google search, PIvot Table Basics, you'll get about 245,000 results. Those on the first page should be useful. – Ron Rosenfeld Dec 07 '15 at 20:50
  • thanks @RonRosenfeld I will check it – maniA Dec 07 '15 at 20:51

2 Answers2

1

Try this:

=SUMPRODUCT((B2:B13=E4)*(A2:A13))

That goes in Cell F4. Fill down.

1

To get the dynamic unique list In D2 =INDEX($B$2:$B$1000,MATCH(0,INDEX(COUNTIF($D$1:D1,$B$2:$B$1000),0,0),0))

To sum by dynamic unique list In E2 =SUMIF(B:B,D2,A:A)

Drag both down

Here are my results (note the values will differ to your results because I only had 1 next to each one because I couldn't be bothered typing in the values in the picture you pasted, please always try to post data that people can copy and paste):

a       3
b       4
f       3
d       2
c       1
0       0
#N/A    0
#N/A    0
#N/A    0
#N/A    0
#N/A    0

You can wrap it in an IF(ISNA(formula),"",Formula) or an iserr formula, whatever you like to counter the #N/A

Dan Donoghue
  • 6,056
  • 2
  • 18
  • 36