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""))"
