Setting a formula in VB that needs to include a null string

  • Thread starter Thread starter Mark E
  • Start date Start date
M

Mark E

I have a formula that I need to reset in a VB script that includes a null
string. Whenever I try to run the macro that resets this formula, I get an
application error #1004. Can anyone tell me how I can do this?

Here is the formula that I type into the cell. The cell has a validation
lookup on it, so the values can change, which clears the formula, but this is
the default.
=IF(ISBLANK($P61),"",INDIRECT(VLOOKUP($P61,TransportTbl,6,FALSE),TRUE))

This cell is clearly dependent on another cell, but there are also cells
dependent on it, that also use the ISBLANK formula to determine if there is a
value here. I would really like an answer that avoids changing the default
value on this cell if the reference cell is also blank.
 
And I take it that the macro is used to re-insert the formula?

Use the following code to insert the formula:
Range("your cell address").formula =
"=IF(ISBLANK($P61),"""",INDIRECT(VLOOKUP($P61,TransportTbl,6,FALSE),TRUE))"

All this on one line though

--
HTH

Kassie

Replace xxx with hotmail
 
Back
Top