Formula or VBA to fill a specified number of cells

[jsolder]

This is a little tricky to explain so I've attached a spreadsheet which sould make things a bit clearer....

In Sheet1 as attached cell B2 contains the number 15, I need the figures in B5:B16 (and possibly more in the future) to be divided by B2. Then the grid between C5:AB16 (which will grow if B2 increases) needs to be populated. Each Month begins in the column which corresponds to it (i.e. Month 1 in C, Month 2 in D, Month 3 in E etc.) and stops when it reaches, in the case of Sheet 1, 15 entries.

Sheet2 illustrates what would happen were cell B2 to change to 18, and the results I am trying to achieve.

Hopefully this makes enough sense for someone to be able to help me!!

Thanks in advance

[Olly]

Try this - put the code in the worksheet module, not a general module;

Please Login or Register  to view this content.

This will update the formulae whenever cell B2 changes.

[boopathiraja]

Hi, Apply this in c5 and drag it down and across

Please Login or Register  to view this content.

[Vikas_Gautam]

Hi,
Try this for one sheet..

Please Login or Register  to view this content.

For all Sheets, Try this..

Please Login or Register  to view this content.

[jsolder]

Olly thanks for your help! Seems to work almost perfectly...
My only issue is that if the number in B2 decreases then values end up remaining from the previous calculations, I tried modifying the code to clear the contents before it updates the formulae but hit a bunch of errors. Where am I going wrong?

Please Login or Register  to view this content.

[Olly]

My original code DOES clear the existing formulae first, with this line:

Please Login or Register  to view this content.

Make sure you have enabled application events by running the following code, then test my code again:

Please Login or Register  to view this content.