Formula to VBV - weeks of cover

brazill23

New Member
Joined
Dec 18, 2018
Messages
11
HI All,

Thank you for your help in advance.Im really struggle to translate one formula to the code. Below formula is calculating weeks of stock cover based on deliveries and demand. Unforgettably it have plenty of "if" slowing down the report. Im looking to translate this to VBA code. Is anyone who can support ?

=IFERROR(IF(K10>K11,K11/K10,IF(K10+L10>K11,1+(K11-K10)/L10,IF(K10+L10+M10>K11,2+(K11-K10-L10)/M10,IF(K10+L10+M10+N10>K11,3+(K11-K10-L10-M10)/N10,IF(K10+L10+M10+N10+O10>K11,4+(K11-K10-L10-M10-N10)/O10,IF(K10+L10+M10+N10+O10+P10>K11,5+(K11-K10-L10-M10-N10-O10)/P10,IF(K10+L10+M10+N10+O10+P10+Q10>K11,6+(K11-K10-L10-M10-N10-O10-P10)/Q10,IF(K10+L10+M10+N10+O10+P10+Q10+R10>K11,7+(K11-K10-L10-M10-N10-O10-P10-Q10)/R10,"8+")))))))),0)

forecast072400900
Open stock1443144371917191719
delivery00100000
weeks of cover3.82.87.16.15.1

<tbody>
</tbody>


In weeks of cover I have above calcualtion. I was think to move to VBA code, but its above my coding skills :(
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Hi.
Typically, as far as I understand, VBA is slower than the built in functions. Maybe a good place to start would be to break up your formula into smaller segments in separate ranges/cells. Please download the HTML helper or something and post a more complete example.
 
Upvote 0

Forum statistics

Threads
1,213,568
Messages
6,114,348
Members
448,570
Latest member
rik81h

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