Archive of Mr Excel Message BoardArchive of Mr Excel Message Board
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?
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?
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
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.
When I type
ActiveCell.Formula _
"==TEXT($B$1,""mm/dd/yy"")&"" ""&(TEXT(B3,""hh:mm AM/PM"")))+0"
Thanks, Russell, for the hint.
...... I have yet to see a formula containing unmatched brackets that works.
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""))"