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.
Peace of mind is found in some of the strangest places.
sorry...forgot the attachment
Peace of mind is found in some of the strangest places.
As far as I can tell, it looks like D12 has a space in it instead of being blank. The formula is returning #VALUE because it can't use text data like that in the formula.
I deleted the contents of D12 and the formula worked when filled down
Edit: oops, I take that back. C26, C28 and B33 have the same problem.
For the record, I found this out by using the Formula Evaluation tool (Tools\Formula Auditing\Evaluate Formula) to step through the formula. It displays the value of each cell. Those cells I mentioned above displayed " " which is what was throwing the formula off.
Hmmm..Have not used that tool before. Thanks. Can mark it solved
Peace of mind is found in some of the strangest places.
Posting Permissions
Reply With Quote
