Hi Ron,

Yes that works brilliantly, thanks so much for that. chiselling away at
the little tuning probs on my ssheet at the moment is a bit of a task.

Thanks so much again,

Cheers,

Aaron.
Ron Rosenfeld wrote:
On 3 Sep 2006 17:53:52 -0700, "Slashman" wrote:

Hi Ron,

I have looked closer at the different items in the macro and have got
the following information.

With the keycell contents being 20000.2 I get the following:

DP= -2
Keycell= 20000.2
AnswerCell=0.29999999999999797
kcFmt= "0.0"
acFmt=""
InStr (;) =0
InStr (.) =2

If I rem out the rept line, it formats the answer cell on the worksheet
simply as +

Hope this debug info helps.

Cheers,

Aaron.

The debug info helps considerably.

The problem is that your KeyCell format does not follow the rules I thought it
would follow. Mea culpa. I thought the KeyCell format would also have
provision for + or -, but it does not. Rather it is exactly how you wrote it.

So given that the KeyCell format will be in the form of "0" optionally followed
by a decimal point and n0's, the following should work:

=============================================
Option Explicit

Sub IncrDP()

Sheets("Working02").Select

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


Set KeyCell = [a1]
Set AnswerCell = [a2]


'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat


DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 1

'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

End Sub
==========================================
--ron