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.
Ron Rosenfeld wrote:
On 31 Aug 2006 17:52:46 -0700, "Slashman" wrote:

Hi Ron,

I have tried to run this macro, I have copied it back for you to see.

================================================= ======

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 = [d10]
Set AnswerCell = [aa12]


'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat


DP = InStr(1, kcFmt, ";") - InStr(1, kcFmt, ".")
If InStr(1, kcFmt, ".") = 0 Then DP = 1


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


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


End Sub

================================================= ========

It stalls on the acFmt line I highlighted and says unable to set
worksheetfunction class.

I added the sheets select line also as I need it to apply to one of
many sheets in my book.

Can you elaborate on this error. I cant find much on the net and the
code doesent give me many places to look as a beginner in VB stuff.

Cheers,

Aaron.

Two quick comments, Aaron, but I'm on my way out and won't have time to look at
that error until Tuesday.

1. I wonder if you are missing a reference in VBA.

2. There is no need to "Select" a worksheet to reference it. Unless you need
to select the worksheet for some other reason, it is usually more effective to
reference it directly.

e.g.
dim ws as worksheet
set ws =
Set KeyCell = ws.range("d10")

etc.


--ron