I have Excel data with column F that has date/time in the format: 11/13/2018 9:33:00 PM Now, I need to get that date displayed in column H as per the formula =TEXT(F2,("MMM-dd")) (I currently do this manually which works fine)

The VBA seems to work up until it hits this formula where it throws me the error: Compile Error Syntax Error

Rest of the code works as expected. I have tried alternate formulas and they seem to work. For example, if I use rng.Value = "words" or rng.Formula = "2+2" the macro works as expected

Code that won't compile:

Sub test()

Dim rng As Range
Dim sht As Worksheet
Dim Lastrow As Long

Set sht = ThisWorkbook.Sheets("Sheet1")
Lastrow = sht.Cells(sht.Rows.Count, "A").End(xlUp).Row

Set rng = Range("H2:H" & Lastrow)
rng.Formula = "=TEXT(F2,("MMM-dd"))"

End Sub

Apologies if this is a dumb question, I'm new to this. Any help is appreciated.


Try extra quotation marks:


Here's the official help, Quotation marks in string expressions.

  • This seems to have been the problem, thank you!!! – Dave Nov 15 '18 at 0:50
  • @Dave please use the green checkmark to indicate that the post answered your question. – Twisty Impersonator Nov 15 '18 at 2:48

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.