"Type mismatch" has me stumped...

[jmpatrick]

Good morning! I'm trying to figure out why this seemingly simple code gives me a "Type mismatch" error.

This works:

Range("Load!G1").Value = "'" & [Text(Now(), "M/D" & " at " & "H:MM AM/PM")]

This does NOT:

Range("Load!G1").Value = "'" & [Text(Now(), "H:MM AM/PM" & " on " & "M/D")]

What am I doing wrong?

 

[Fluff]

Try it like

Range("Load!G1").Value = "'" & [Text(Now(), "H:MM AM/PM" & " o\n " & "M/D")]

 

[6StringJazzer]

If you put the second formula in a cell, it will return #VALUE!. I have not dug down to understand why.

I prefer to use built-in VBA functions rather than evaluating a worksheet formula. Also there is no reason to prefix with "'" because you are creating a text string anyway.

This will work:

Range("Load!G1"").Value = Format(Now, "h:mm AM/PM") & " on " & Format(Date, "m/d")

 

[jmpatrick]

Try it like

Range("Load!G1").Value = "'" & [Text(Now(), "H:MM AM/PM" & " o\n " & "M/D")]

Thanks! I have no idea why that works.

 

[Fluff]

I'm guessing that the "n" has a special meaning to the text function & so it needs to be escaped using the "\"

 

[6StringJazzer]

After seeing Fluff's answer, I streamlined my version. The same format strings apply to the Format function.

Range("Load!G1"").Value = Format(Now, "h:mm AM/PM o\n m/d")

 

[Joe4]

I'm guessing that the "n" has a special meaning to the text function & so it needs to be escaped using the "\"

In certain programs, I have seen them use "n" to present "months", as they already use "m" for minutes.

 

[6StringJazzer]

In both VBA Format and the worksheet TEXT function, Excel parses the string for context. It recognizes if "m" is used as part of a time expression, and if so uses minutes. Otherwise it treats it as month. I cannot find any documentation for custom formatting or the TEXT function that mentions "n" as a format code.

Using "n" in a format string for TEXT results in a #VALUE! error. However, using it in the VBA function Format it means "minutes".

Format function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com