Number Format

[Aaron]

Hi,

Im using >
..NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & "
\kg" as excel says it cant set the number format.

Why should it care about a text inside brackets or is it limited to
just one text character? How can I make this work?

TIA

Aaron.

 

[Rick Rothstein]

It would have been better to post your message back at the original thread
where its content would have been in context. As for your your question, the
'g' seems to have meaning to the NumberFormat, although I'm not sure what it
is at the moment (and I'm about to go to sleep for the evening, so I won't
look for it until morning here), but the 'k' doesn't. Try using " k\g". Here
are your two macros with that change included...

Sub DPshiftdown()
With Range("Q16")
If .NumberFormat = "0 k\g" Or .NumberFormat = "General" Then Exit Sub
.NumberFormat = Replace(.NumberFormat, " k\g", "")
.NumberFormat = Trim(Left(.NumberFormat, Len(.NumberFormat) - 1))
If Right(.NumberFormat, 1)= "." Then .NumberFormat = Val(.NumberFormat)
.NumberFormat = .NumberFormat & " k\g"
End With
End Sub

Sub DPshiftup()
With Range("Q16")
.NumberFormat = Replace(.NumberFormat, " k\g", "")
If .NumberFormat = "0" Or .NumberFormat = "General" Then
.NumberFormat = "0.0 k\g"
Else
.NumberFormat = .NumberFormat & "0 k\g"
.NumberFormat = Replace(.NumberFormat, "0000", "000 0")
End If
End With
End Sub

 

[Ron Rosenfeld]

Hi,

Im using >
.NumberFormat & " \g" sucessfully, but I cant use .NumberFormat & "
\kg" as excel says it cant set the number format.

Why should it care about a text inside brackets or is it limited to
just one text character? How can I make this work?

TIA

Aaron.

Gee, it sure would help if you posted in your original thread.

As best I can tell, you are formatting within VBA. And I am also assuming that
you want to display something like

123 g

As such, your format string is incorrect.

For example, if your format string was "General", then the above would resolve
to either

General \g

or
General \kg

The problem is that you can only follow a slash with a single character.

Two solutions:

..NumberFormat & "\k\g"

which resolves to General \k\g

or

..NumberFormat & """ kg""" which would resolve to

General" kg"
--ron

 

[Aaron]

Gee, it sure would help if you posted in your original thread.

As best I can tell, you are formatting within VBA.  And I am also assuming that
you want to display something like  

        123 g

As such, your format string is incorrect.

For example, if your format string was "General", then the above would resolve
to either  

        General \g

or
        General \kg

The problem is that you can only follow a slash with a single character.

Two solutions:

.NumberFormat & "\k\g"

which resolves to       General \k\g

or

.NumberFormat & """ kg"""  which would resolve to

        General" kg"
--ron

Sorry I am new to the groups and thought after a topic was thanked it
was sort of closed and not looked at again.

I now understand my problem is that you can only follow the \ with one
character.

The g is for grams and I also would like kg for kilograms and lbf for
pounds.

I have now made the line " \k\g" for kg and it works great. I assume I
can use " \l\b\f" for pounds also but I havent tried that yet.

Thanks again everyone.

Aaron.

 

[Ron Rosenfeld]

I now understand my problem is that you can only follow the \ with one
character.

The g is for grams and I also would like kg for kilograms and lbf for
pounds.

I have now made the line " \k\g" for kg and it works great. I assume I
can use " \l\b\f" for pounds also but I havent tried that yet.

Thanks again everyone.

Aaron.

You're welcome. Glad to help.

Please not also that the other option is to use "double" the quote marks when
you want to include a quote within the quotes

eg: """ kg"""

or

""" lbf"""

--ron