Automated Autosum

[Aziona]

Hi

I would like to seek some advice on how I can do the following. I have a macro(I use a do while loop to check and insert the extra row) that sorts out data in a certain format by inserting a line(row) in between 2 rows effectively separating the data set. After running my macro, I am presented with the following data:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Serial Num[/TD]
[TD]Data1[/TD]
[TD]Volume[/TD]
[TD]Data3[/TD]
[TD]Data4[/TD]
[TD]Data5[/TD]
[TD]Count1[/TD]
[TD]Count2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]August[/TD]
[TD]72.52[/TD]
[TD]Sell[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD]12345[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]August[/TD]
[TD]56.00[/TD]
[TD]Sell[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]12546[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]August[/TD]
[TD]71[/TD]
[TD]Buy[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD]12546[/TD]
[TD]Apple[/TD]
[TD]2[/TD]
[TD]August[/TD]
[TD]58[/TD]
[TD]Buy[/TD]
[TD]2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]12546[/TD]
[TD]Apple[/TD]
[TD]1[/TD]
[TD]August[/TD]
[TD]22[/TD]
[TD]Buy[/TD]
[TD]1[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

How do I use macro to Autosum Cell "H5" to get 2 and Cell "H9" to get 4 and at the same time I wanna do a formula(Weighted average) on Cell "F5" like this: Sumproduct(F3:F4,D3:D4)/Sum(D3:D4).

I have been cracking my brain on how i can actually automate this function in that the macro checks and input a formula so that i can do it for my whole data set down to say like 1000 rows. I have some problem trying to get the macro to recognize that cell f3 and cell f4 is the dataset i wanna use. this is because sometimes there may be 5 sets of say "12345" and 2 sets of "12546" etc(Changes day to day). i tried macro recording to analyze the function control down(arrow key) to record everything but it doesnt seems to work as well when i comes to autosum or formula like sumproduct. i figure that i would probably have to deconstruct the formula in a more simple terms or is there a more efficient way to do this?

I use to use Range.value = "=formula" to pass it in but the issue comes up when i have to make my Range dynamic as the data changes constantly or when i have to break up my formula etc.

Any help would be greatly appreciated.

Thanks.

 

[Fluff]

Hi & welcome to MrExcel
If your data in col H is values rater than formulae, try

Sub addFormula()
   Dim Rng As Range
   For Each Rng In Range("H2", Range("H" & Rows.count).End(xlUp)).SpecialCells(xlConstants).Areas
      Rng.Offset(Rng.count).Resize(1, 1).Formula = "=sum(" & Rng.Address & ")"
      Rng.Offset(Rng.count, -2).Resize(1, 1).Formula = "=sumproduct(" & Rng.Offset(, -2).Address & "," & Rng.Offset(, -4).Address & ")/Sum(" & Rng.Offset(, -4).Address & ")"
   Next Rng
End Sub

 

[Aziona]

Hi Fluff!

Thanks for the welcome! Let me try it out and see how this works. I see how you actually did this. was this using relative or absolute for range control? seems like relative?

Thanks.

 

[Fluff]

The formulae are absolute references.

 

[Aziona]

Thanks Fluff for the help! I got it to work! and even got to implement other types of formula using this.

 

[Fluff]

Glad you got it sorted & thanks for the feedback