Aziona

New Member
Joined
Aug 24, 2018
Messages
3
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:

ABCDEFGHI
1
2Serial NumData1VolumeData3Data4Data5Count1Count2
312345Apple1August72.52Sell11
412345Apple2August56.00Sell11
5
612546Apple2August71Buy11
712546Apple2August58Buy21
812546Apple1August22Buy11
9

<tbody>
</tbody>

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.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Hi & welcome to MrExcel
If your data in col H is values rater than formulae, try
Code:
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
 
Upvote 0
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.
 
Upvote 0
The formulae are absolute references.
 
Upvote 0
Thanks Fluff for the help! I got it to work! and even got to implement other types of formula using this.
 
Upvote 0
Glad you got it sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,212,927
Messages
6,110,700
Members
448,293
Latest member
jin kazuya

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top