This is not really a VBA question rather a formula question. I have attached a spreadsheet of the problem. Looking at Col H where the formula is the cell H8 gives the desired result. However if you copy it down the column at one point you get the #value! error. Proceeding down further it begins to work again. Cannot figure if it is a formatting problem or not. Have tried everything I can think of. If anyone has any ideas I would appreciate it. Sheet one has the same formula and works fine.