Hi Bob,

in q17 is the text that could be either N g or kg, but in x4 the answer
could be anything at all any number at all so the if and elseif wont
work. But it gave me an understanding of how that works.

Cheers,

Aaron.

Bob Phillips wrote:
maybe this is what you want

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Dim suffix As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply more
DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

'acFmt = "+" & acFmt & ";-" & acFmt & ";0"

If Range("Q17").Value = 1000 Then
suffix = """kg"""
ElseIf Range("Q17").Value = 1000000 Then
suffix = "\N"
Else
suffix = "\g"
End If
acFmt = "+" & acFmt & suffix & ";-" & acFmt & suffix & ";0" & suffix

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt



--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Slashman" wrote in message
ups.com...
Ok, in debug mode, acFmt looks correct, but I get the message unable to
set the range class.

I added these g, kg or N cell info to the acFmt line:

acFmt = "+" & acFmt & [q17] & ";-" & acFmt & [q17] & ";0" & [q17]

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt

Cheers,

Aaron.


Slashman wrote:
Hi Ron

I didn't post the macro because I thought it might confuse more.


////////////////////////////////////////////////////////////////////////////
///////////////////////////////

Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [q16]
Set AnswerCell = [x4]

'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat

DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add a number here to apply
more DP

'Add one decimal place to the KeyCell number format
acFmt = "0." & Application.WorksheetFunction.Rept("0", DP)

'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0"

acFmt = "+" & acFmt & ";-" & acFmt & ";0"

'Assign new format to answer cell
AnswerCell.NumberFormat = acFmt


////////////////////////////////////////////////////////////////////////////
//////////////////////

I have tried going into format and then custom and adding the \g after
the code and it works nicely.

EG: +0.000\g;-0.000\g;0\g

But the other thing I forgot to mention, is that I sometimes have kg
and N, not always g, how can I automate for this? There is a cell, q17
that will have the sheets g or kg or N in it though if this helps.

Thanks,

Aaron.

Ron Rosenfeld wrote:
On 10 Sep 2006 18:13:11 -0700, "Slashman"
wrote:

Hi,

I have a cell that is formatted with a macro to change the number 10
to
+10.0000

I would also like to add a text letter of g to this same cell so it
reads +10.0000g

When I try and use the & function in the formula it just gives me
100g
and removes the + formatting and the trailing DP zeroes.

Is there a way of combining these?

Thanks in advance,

Aaron.

Cannot be more specific since you chose not to post the macro you are
using.
However, in general, you can accomplish what you want by adding a \g
to the
format string (after the decimal zeros) within the macro.

In other words, if the format string in the macro is something like:

"+0.0000_)", you could change it to "+0.0000\g_)"


--ron