Quotations in a formula, that is inserted by a macro

Posted by Ben on April 21, 2001 3:09 AM

I have wrote this macro below, but the formula that it puts into the cell contains quotation marks. So when I try to run it, it tells me that they are "Expected: End Of Statement"

Could anyone please tell me how to make the formula that is put into the cell have quotation marks around the M and td, which is the day the macro was run in the format of dd/mm/yy.

The macro is designed to display in the cell how long since the formula was inserted.

Sub test()
td = Format(Now(), "dd/mm/yy")
Sheets("Sheet1").Range("A2").Formula = "=DATEDIF(" & "td" & ",TODAY(),"M")"
End Sub

Posted by Dave Hawley on April 21, 2001 3:15 AM

Hi Ben

Try this

Sub test()
Dim td As Date
td = Format(Now(), "dd/mm/yy")
Sheets("Sheet1").Range("A2").Formula = "=DATEDIF(" & td & ",TODAY(),""M"")"
End Sub

Dave
OzGrid Business Applications

Posted by IFM on April 21, 2001 3:17 AM

Sheets("Sheet1").Range("A2").Formula = "=DATEDIF(" & td & ",TODAY()," & """M""" & ")"

Posted by Dave Hawley on April 21, 2001 3:19 AM

Ben a Tip!

Ben, putting formulas in cells can be tricky at time via VBA so as a tip, put the formula in a cell as normal, select the cell, Record a macro pushing F2 then enter, then stop recording. This will write the formula for you and all you need to do is modify it.

I mention this on my "VBA Tips and Tricks" page on my Website.

Dave

OzGrid Business Applications

Posted by Ben on April 21, 2001 3:37 AM

Thanks but neither formulas but quotations around tb

Thanks but the macro doesn't put quotations around tb. So what should be =DATEDIF("21/4/1",TODAY(),"M"), is =DATEDIF(21/4/1,TODAY(),"M")

How do I put quaotations around todays date?

This makes the answer 1215 instead of 0
Thanks

Posted by Ivan Moala on April 21, 2001 3:41 AM

Re: Thanks but neither formulas but quotations around tb

Sheets("Sheet1").Range("A2").Formula = "=DATEDIF(" & """" & td & """" & ",TODAY()," & """M""" & ")"

Ivan

Posted by BE on April 21, 2001 3:51 AM

Thanks

Posted by BE on April 21, 2001 3:51 AM

Thanks

Posted by Ben on April 21, 2001 3:51 AM

Thanks

Posted by Ivan Moala on April 21, 2001 3:51 AM

Re: Thanks but neither formulas but quotations around tb

Ben
Did you try Daves Tip, I usually use this tech.

ivan

Posted by Dave Hawley on April 21, 2001 4:23 AM

Ok, I have to ask.....

Why do you want the formula;
=DATEDIF("21/04/2001",TODAY(),"M")

This isn't a valid formula, it will either return zero or #NUM!

I'm curious.

Dave

OzGrid Business Applications

Posted by Ivan Moala on April 21, 2001 4:45 AM

Re: Ok, I have to ask.....

Dave

The DATEDIF function is a valid worksheet function that computes the difference between two dates. This function is neither documented nor supported by Microsoft. It is however described in the Excel2000 help file. The function itself is available in Excel5, Excel95, Excel97, and Excel2000.

Ivan

Posted by Dave Hawley on April 21, 2001 5:04 AM

Re: Ok, I have to ask..... Yes but...

Ivan
I realise this, I have suggested it here a few times myself.

Arrrrhhh!!
Ok, I just realised something, I tried:
=DATEDIF("21/4/2001",TODAY()+25,"m")

and it returned zero, so I thought it was back to front. But it it will only return a WHOLE month. I need some more caffiene!, Lots more! :o)

Dave

OzGrid Business Applications

Posted by Ivan Moala on April 21, 2001 5:14 AM

Re: Ok, I have to ask..... Yes but...

Dave
I think I need some caffiene too :-) don't
forget NZ time 2 hours ahead of Aust time.
Now after midnight and just cruising the sites.

time for coffee now.

cheers

Ivan I realise this, I have suggested it here a few times myself. Arrrrhhh!!