MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Complie Error: Expected End of Statement when entering code that enters a formula in a cell


Posted by Chris on December 13, 2001 12:07 PM

Im using VBA to enter a formula in a cell. Here's the formula:
=TEXT($B$1,"mm/dd/yy")&" "&(TEXT(B3,"hh:mm AM/PM")))+0

When I type
ActiveCell.Formula _
"==TEXT($B$1,"mm/dd/yy")&" "&(TEXT(B3,"hh:mm AM/PM")))+0" in my VBA module, I get a Visual Basic error: Expected: end of statement when it hits the first set of quotation marks in my formula.

Is there a proper syntax to use so the macro doesn't mistake the quotation marks in my formula for the quotation marks at the end?


Posted by Andromache on December 13, 2001 12:30 PM


Use the macro recorder to get the correct syntax.

Anyway, it doesn't look like your formula is correct - there is an odd number of brackets and the +0 can't be used in conjunction with concatenating text. What result are you looking for from the formula?

Posted by Russell Hauf on December 13, 2001 12:32 PM

Chris,

Can you post the formula as you would like it to look when it's in the cell (not in vba fomat)?

But in general, use two double-quotes in a string to get one in the final string. For example,

strX = "Then she said, ""Wow"", and fainted."

Would give you the string:

Then she said, "Wow", and fainted.

You can also use Chr(34):

strX = "Then she said, " & Chr(34) & "Wow" & Chr(34) & ", and fainted."


-Russell

Posted by Chris on December 13, 2001 12:43 PM

I don't like the macro recorder because it records the formula in R1C1 Format. I'd like to avoid it, if possible.

In $B$1, I have the date (12/3/01), and in B3, I have the current time (12:00 AM). The result I am after is 12/3/01 12:00 AM. The formula works, and I just want my macro to type it for me.

Posted by -- The formula works, Honest! --- Chris on December 13, 2001 12:51 PM

The formula will be this: =TEXT($B$1, "mm/dd/yy")&" "&(TEXT(B3, "hh:mm AM/PM")))+0

In $B$1, I have the date (12/3/01), and in B3, I have the current time (12:00 AM). The result I am after is 12/3/01 12:00 AM. The formula works, and I just want my macro to type it for me.

Posted by Chris on December 13, 2001 1:00 PM

OOps, simple answer....

This is how my VBA should read:

When I type
ActiveCell.Formula _
"==TEXT($B$1,""mm/dd/yy"")&"" ""&(TEXT(B3,""hh:mm AM/PM"")))+0"

Thanks, Russell, for the hint.

Posted by Andromache on December 13, 2001 1:35 PM

Honest? I don't think so ........


...... I have yet to see a formula containing unmatched brackets that works.

Posted by Andromache on December 13, 2001 1:37 PM

Re: OOps, simple answer....


I think the syntax you are looking for is :-

ActiveCell.Formula = _
"=TEXT(" & [B1].Address & ",""mm/dd/yy"")&"" ""&(TEXT(" & [B3].Address(False, False) & ",""hh:mm AM/PM""))"