VBA Code that Sums on a Loop Based on Criteria and Linked to Calender

mkeys4

New Member
Joined
Jul 17, 2013
Messages
1
Hi guys,

What I'm trying to do is too complex for a regular Excel formula, so I need a VBA. Unfortunately, I know very little about VBA, so I'm really counting on this forum. I think this might be a challenging VBA, but hopefully fun too!

As it is now, column CH has a formula that takes the sum of the top three and bottom three values in columns BV:CG. The criteria for "top" and "bottom" is based on the ranks in AX:BH--i.e. if a cell's corresponding "rank" is in the top three highest or lowest in all of the ranks of that row, then that cell is added to the sums in CH. *The formula also only runs if the sums of all corresponding ranks on that row add up to 45* Formula=(IF(SUM($AX4:$BH4)>=45,SUM(SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4))+SUM(SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)),FALSE))

As it is now, it's wrong.
First: Instead of using different ranks each day, I need to use the same criteria for the whole work week (5days). In other words, I need to target these parts of the above formula "SUMIF($AX4:$BH4,LARGE($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and "SUMIF($AX4:$BH4,SMALL($AX4:$BH4,{1,2,3}),$BV4:$CF4)" and change them so that the large{1,2,3} and small {1,2,3} of $AX4:$BH4 hold for BV4:CF8, i.e. the whole week.
Second: The criteria on the last day of the proceeding loop is the criteria for the first day of the next loop. In other words, thought about in a real life scenario, one can only make a decision for that week based on the criteria of the day before.
Third: If the loop hits a "FALSE" statement (which would happen if the sum of the ranks is less than 45), I need excel to take the next available criteria, and apply that to the next 5 days.
Fourth: This (above statement) means that the loop needs to be linked to a calender. I can't just apply the same criteria to 5 cells, the next criteria to the next 5 cells, ect, because on the FALSE days, there would not be a sum, but instead the sum would pick back up at the next available criteria, and THEN apply that to the next 5 cells.


Even a partial code or input would be helpful. I'm really running on nothing.
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

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