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!
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!
VBA Code:
Activesheet.Range("W21").Formula = "=IF(B21<>"""",0,"""")"