Excel setting numberformat in range with unusual text descriptor 2

[longestdrive]

Hi All

I'm stuck on a simple task.

I can format a range of cells in excel to set a descriptor after the number. using the number format 0.0 "hrs".

However I want to repeat the format using vba via numberformat. I'm referencing excel via MS Project

i've used this code:

.NumberFormat = "0.0 hrs"

When run it comes up with an error stating number format invalid.

I've tried

.NumberFormat = "0.0 'hrs'" but again invalid

How can I set the number format to use a unique descriptor?

Thank you for your help

 

[SkipVought]

Hi,

This is really a forum68 question.

Format > Cells: Custom Category -- 0.0 "Hrs"

Use your macro recorder if needed.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[AnotherHiggins]

With the utmost respect, I disagree with Skip. This is a VBA question.

The problem you're running into is that you want to use quotes in the format, but you have to use quotes AROUND the format in VBA.

When To make quotes show up when surrounded by quotes in VBA, you need to double up the inner quotes.

Like this:
[tab].NumberFormat = "0.0 [highlight]""[/highlight]hrs[highlight]""[/highlight]"

The same would hold true if you were creating a message box and wanted the prompt to read

This is a "quote" test

You would code that like
[tab]MsgBox "This is a ""quote"" test"


[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Use your macro recorder if needed.

Self answering!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[longestdrive]

Hi All

Thank you for your responses. I did as suggested and used the macro - I often overlook this solution so apologies.

The solution is as anotherhiggins pointed out, the double quotes. Interestingly when I tried to type the code it didn't like it but a copy and paste from the recorded macro did work..

Thank you, my problem is solved

Ta