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:
<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.
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:
A | B | C | D | E | F | G | H | I | |
1 | |||||||||
2 | Serial Num | Data1 | Volume | Data3 | Data4 | Data5 | Count1 | Count2 | |
3 | 12345 | Apple | 1 | August | 72.52 | Sell | 1 | 1 | |
4 | 12345 | Apple | 2 | August | 56.00 | Sell | 1 | 1 | |
5 | |||||||||
6 | 12546 | Apple | 2 | August | 71 | Buy | 1 | 1 | |
7 | 12546 | Apple | 2 | August | 58 | Buy | 2 | 1 | |
8 | 12546 | Apple | 1 | August | 22 | Buy | 1 | 1 | |
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.