VBA - Worksheet Function IsBlank?

simongeds

New Member
Joined
Feb 2, 2008
Messages
28
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.

Code:
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Colin Legg

MrExcel MVP, Like totally RAD man
Joined
Feb 28, 2008
Messages
3,497
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

Code:
    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

New Member
Joined
Feb 2, 2008
Messages
28
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,485
Messages
5,596,441
Members
414,065
Latest member
kamlkham

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top