#Value! errors

[thecat23]

Hi all,

I am trying to assign a formula to a cell "W21" that is reference to cell"B21", if it's Not empty, then the cell "W21" = 0, if empty then "W21" = "", which is nothing.

Then the cell "W21" is used by other cell to do numerical calculations, to simply it, say another cell is = 100 + "W21", this would return a error #Value!, but if I type = Sum(100; W21), this is working fine. The issue is that the formula in other cell is too complicated to replace it with simple functions, Sum, subtraction, etc. is any other way to bypass this? it seems the cell "W21" contains a formula, so when a "" is assigned to the cell, it does not treated as a empty space with isblank(). so when it was used by other formula, a error would occur.

any ideas? thanks!

Activesheet.Range("W21").Formula = "=IF(B21<>"""",0,"""")"

 

[RoryA]

You can't do mathematical operations on an empty string so you either have to just return 0 instead of "" (which makes your formula pretty pointless) or adjust your other formulas to account for W21 having an empty string in it.

 

[thecat23]

You can't do mathematical operations on an empty string so you either have to just return 0 instead of "" (which makes your formula pretty pointless) or adjust your other formulas to account for W21 having an empty string in it.

Thanks for your comments.
I was trying to make the cells display nothing if the lookup cell is empty, and autofill the cell with a value when the lookup cell is filled. I guess I have to forget about this one.

Thanks anyway.

 

[Georgiboy]

Have you looked at the IFERROR function, you could wrap your current formulae with it and ask it to return "" instead of the error?

 

[thecat23]

Have you looked at the IFERROR function, you could wrap your current formulae with it and ask it to return "" instead of the error?

Hi Georgiboy,

IFERROR still returns a "" which is the cause of the error. Unless it returns a empty number like 0 then it's fine. Then it kind of defeat the purpose since I was trying to get a blank cell when the lookup cell is has nothing in it instead of a number 0. I guess I could use IFERROR in the other cells that does the calculations to treat this cell as 0 if error is obtained, but this would take too long to fix.

I will keep thinking the ways around it.

Thanks for your comments.

 

[Alex Blakenburg]

Do you want to show us an example of a follow on formula and what result you are hoping for if W21 is "" ?
Does it help to know that
= 100 + N(W21)
would treat "" as 0.