0

I am trying to put text in front of a date by using a VBA Macro in excel.

I tried to use the .Formula object but I keep getting a Compile error: Expected: end of statment and it highlights the " right before PCS. I would assume this would just type this formula into the cell because this formula works just by typing it in. Could I use .Value instead or could I put in an On Error Resume Next and try to bypass the error?

Range("A1").Formula = "=TEXT("PCS USED AS OF ","")&TEXT(TODAY(),"mm/dd/yyyy")"

Thanks for your input! (and output)

BigBen
  • 46,229
  • 7
  • 24
  • 40
Kyle Slazakowski
  • 1
  • You'd probably want `Range("A1").Formula = "=""PCS USED AS OF ""&TEXT(TODAY(),""mm/dd/yyyy"")"` instead. Note that you need to double up quotes. Also, do you really want this volatile formula in your cell or just the value? – JvdV Nov 01 '22 at 13:10
  • `Range("A1").Value = "PCS USED AS OF " & Format(Date, "mm/dd/yyyy")` would maybe do what you are after without being a volatile formula. – JvdV Nov 01 '22 at 13:18
  • 1
    Range("A1").Value = "PCS USED AS OF " & Format(Date, "mm/dd/yyyy") worked perfectly. Can I ask what you meant by a "Volitial formula" I have some other .formula = strings in my code that probably don't need to be there. – Kyle Slazakowski Nov 01 '22 at 15:01
  • Volatile means that the formula uses functions (`TODAY()` in your case) that will recalculate everytime something chanes anywhere in the workbook (and more). Rather avoid these since too many will start hitting the performance of your workbook. – JvdV Nov 01 '22 at 15:03

0 Answers0