Hi,

I have a custom number format that prefixes positive numbers and
negative numbers with + and - signs, and a macro line that makes a cell
DP increase by one, based on a key cell DP placement.

IE Key cell DP is: 0.000 and answer cell is therefo 0.0000

Because the number of DP changes for the key cell each time I use the
spreadsheet, I cant
just have it formatted for custom all the time for the + and - prefix,
as when I run my macro to set the DP of that cell, it overrides the
custom formatting for the + or -.


I need both things to happen, is it possible or is there another way to

simultaneously change the DP of the cell and apply the custom number
format for + and - numbers each time?

Cheers,

Aaron.

On 27 Aug 2006 15:23:57 -0700, "Slashman" wrote:

Hi,

I have a custom number format that prefixes positive numbers and
negative numbers with + and - signs, and a macro line that makes a cell
DP increase by one, based on a key cell DP placement.

IE Key cell DP is: 0.000 and answer cell is therefo 0.0000

Because the number of DP changes for the key cell each time I use the
spreadsheet, I cant
just have it formatted for custom all the time for the + and - prefix,
as when I run my macro to set the DP of that cell, it overrides the
custom formatting for the + or -.


I need both things to happen, is it possible or is there another way to

simultaneously change the DP of the cell and apply the custom number
format for + and - numbers each time?

Cheers,

Aaron.

You could maybe use a macro like this to set both:

=======================================
Option Explicit
Sub IncrDP()
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [a3]
Set AnswerCell = [b3]

'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
======================================

--ron

Thanks alot for this I will try it when I am in work on Friday.

Cheers again,

Aaron.
Ron Rosenfeld wrote:
On 27 Aug 2006 15:23:57 -0700, "Slashman" wrote:

Hi,

I have a custom number format that prefixes positive numbers and
negative numbers with + and - signs, and a macro line that makes a cell
DP increase by one, based on a key cell DP placement.

IE Key cell DP is: 0.000 and answer cell is therefo 0.0000

Because the number of DP changes for the key cell each time I use the
spreadsheet, I cant
just have it formatted for custom all the time for the + and - prefix,
as when I run my macro to set the DP of that cell, it overrides the
custom formatting for the + or -.


I need both things to happen, is it possible or is there another way to

simultaneously change the DP of the cell and apply the custom number
format for + and - numbers each time?

Cheers,

Aaron.

You could maybe use a macro like this to set both:

=======================================
Option Explicit
Sub IncrDP()
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String

Set KeyCell = [a3]
Set AnswerCell = [b3]

'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
======================================

--ron

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.

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

Hi Ron,

Thats fine, whenever you get the chance to have a look at this for me
would be great.

I have tried playing with the suggestion you wrote but no luck.

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

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