Dual Formatting in Macro

[Slashman]

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 therefore: 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.

 

[Ron Rosenfeld]

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 therefore: 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

 

[Slashman]

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

Cheers again,

Aaron.

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 therefore: 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

 

[Slashman]

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.

 

[Ron Rosenfeld]

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

 

[Slashman]

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 .

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

 

[Slashman]

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.

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

 

[Ron Rosenfeld]

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

 

[Slashman]

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.

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

 

[Ron Rosenfeld]

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.

You're welcome. Glad to help. Without your debugging output, it would have
taken much longer.

Best wishes
--ron