Selected Answer
If you want to use VBA you shouldn't limit your ability to do so by insisting on using worksheet formulas as well. Its like wrestling with one arm tied behind your back and, considering your lack of experience, the other one broken lol:. Therefore I suggest a purely worksheet based solution at first. In this thread I shall show you how to avoid zeroes caused by copied blank cells.
In the attached workbook I have modified the formulas on the XTC tab. The idea is that you can copy the formulas down for many more rows than you will ever need. All of them will stay blank if the referenced cells in the WEEKLY tab are blank except for the last row in column A which will show "END" against the first row which is blank on the WEEKLY tab.
I draw your attention to the formula in XTC!J8 and its simplified form in J9.
[J8] =IF(SUM($F8),MAX(SUM($F8)-40,0),"")
[J9] =IF($F9,MAX($F9-40,0),0)
J8 refers to [F8] =IF(WEEKLY!$Q13,MIN(WEEKLY!$Q13,40),"") which has a value of "" (because WEEKLY!$Q13 is blank) which looks blank but is a text strong of 0 byte length, meaning it is not zero. In fact, it isn't a number at all. Technically speaking it's a word. Therefore, when J8 asks if it is a number >0 the answer is an error. To overcome this problem J8 asks if SUM(F8) > 0. Excel's SUM() function interprets "" as 0.
F9 has no formula in it. It has a format of General and asked if it is zero it responds in the affirmative. Therefore the formula in J9 doesn't require SUM(F9). The plain reference to F9 will do. Copy F8 to F9 and J9 will show an error. Then change the formula in F9 to =IF(WEEKLY!$Q14,MIN(WEEKLY!$Q14,40),0) - replacing the "" with 0 - and J9 works again but F9 shows a zero which you don't want.
How to get rid of this zero is demonstrated in J9 (which also returns a zero which, however, is invisible. This has been achieved by setting a number format which displays a blank when the cell's value is zero. In the case of J9 that format is 0.00;0.00; For F9 the format should be 0;-0; Try it out on F9. The trick is that a cell format has 4 settings, separated by semi-colons: positive number, negative number, zero and text. So, you set the format as 0 for positive (meaning integers, no decimals), -0 for negative and Nothing for zero, indicated by the semi-colon with nothing following it. BTW, a custom format of ;;; would display nothing, whatever you enter in the cell such formatted.
Look at the formulas on your PBU tab. They are all upset by the null strings I introduced on the XTC tab. I recommend you change all those formulas to return 0 instead of "" and format the cells to hide the zeros. Then transfer the method introduced on the XTC tab to the way the PBU tab works and your problem should be solved. Without VBA.
