I have worte below code and now i want to add text with date and Time like this.
Last Update on: 3/12/2021 6:38:43 AM
I tried with below code but its not working any help will be appreciated.
With Sheet9.Range("H8")
.Value = Now()
.NumberFormat = "Last update on: " & "mm/dd/yyyy h:mm:ss AM/PM"
End With
The number format in Excel (not VBA) would look like "Last Update on " dd/ mmm yyyy- the fix text needs to be put into quotes.
When setting the number format using VBA, you need to tell VBA (not Excel) that you want to have a quote within a string. You do this by double the quotes. Look at the following statement: The first quote tells VBA that a string starts. The 2nd and 3rd quote tells VBA that you don't want to end the string but to put a quote character inside the string.
.NumberFormat = """Last Update on "" dd/ mmm yyyy"
"Last update on: " isn't really a valid format.
You could try to add the text in after the formatting.
Usint Range.Text instead of .Value should retain any formatting.
With Sheet9.Range("H8")
.Value = Now()
.NumberFormat = "mm/dd/yyyy h:mm:ss AM/PM"
.Value = "Last update on: " & .Text
End With
If we are going to have it as a text anyway, we can employ FunThomas method to do it all on one line.
.Value = Format(Now(), """Last update on: "" mm/dd/yyyy h:mm:ss AM/PM")
