VBA - Worksheet Function IsBlank?

[simongeds]

Hi All

Can you please help with a problem I am having with some VBA code. As part of the macro I want the code to enter a formula into a cell. The formula is fairly simple, just saying.....if cell A36 is blank then return blank, else if cell A36 is less than or equal to Today's Date then return the latest Position cell reference.

However, writing the formula in this way in the code returns 'FALSE' in the cell because of the way the code interprets all the ""s. Am I just being stupid as I can't quite get this to work? I'm sure it should be quite easy, but I can't quite work out how to write it so that it works.

LastPosition = Activecell.Address(False, False)
 
[B]Worksheets("Position").Range("F37").Formula = "=if('Date!'A36="","",if('Date!'A36<=Today,0,'Date!"' & LastPosition & "))"[/B]

Thanks for your help

Simon

 

[Colin Legg]

Hi Simon,

There appears to be a few mistakes in your formula.
The single quotes ' (which aren't really required here because the name of the Date worksheet does not have any spaces) should not wrap around the exclamation marks !
The TODAY worksheet function should be followed by empty brackets ()
The double quotes "" need to be doubled up to """"

So this would give:

    Dim LastPosition As String, sFormula As String
 
    LastPosition = ActiveCell.Address(False, False)
 
    sFormula = "=if('Date'!A36="""","""",if('Date'!A36<=Today(),0,'Date'!" & LastPosition & "))"
 
    Worksheets("Position").Range("F37").Formula = sFormula

Is that what you're looking for?

 

[simongeds]

Hi Colin

Yes that is exactly what I was after! That works fine now.....Thanks very much

Oh I see is that what the single quotes are for! I did have quite a long 2 word worksheet name in my formula and so cut it down to just 'Date' so that it was easier to read, but didn't realise that the single quotation marks were only put in when the formula referenced a worksheet with a space in the name. You've taught me something else there!

The Today in the formula was just a named cell with Today's Date in it, but I didn't explain that very well.

Thanks again for your help

Simon