Number Format

[Aaron]

I have this macro code:


Range("u22:u26").NumberFormat = _
Range("ad22").NumberFormat '(number format in ad22 is "0.00")

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

Set KeyCell = [q16]
Set AnswerCell = [u22:u26] 'set + - sign to cells

'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


If I rem out the first 2 lines with a ' it works and places a + or -
where it should, but if I perform the first 2 lines just before that
first, it doesent apply the + or -.

How can I make the two work together so it does +0.00 or -0.00?

TIA,

Aaron.

 

[Rick Rothstein]

Providing the code was a good thing... the only thing you forgot to do is
tell us in words what it is supposed to be doing (so we don't have to fight
our way through your logic). What do you have and what is this code supposed
to be doing to it?

 

[Aaron]

Providing the code was a good thing... the only thing you forgot to do is
tell us in words what it is supposed to be doing (so we don't have to fight
our way through your logic). What do you have and what is this code supposed
to be doing to it?

--
Rick (MVP - Excel)

I have this macro code:

Range("u22:u26").NumberFormat = _
Range("ad22").NumberFormat '(number format in ad22 is "0.00")

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

Set KeyCell = [q16]
Set AnswerCell = [u22:u26] 'set + - sign to cells

'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

If I rem out the first 2 lines with a ' it works and places a + or -
where it should, but if I perform the first 2 lines just before that
first, it doesent apply the + or -.

How can I make the two work together so it does +0.00 or -0.00?

Aaron.

Erm well I have a range of cells that I need to have a positive sign
on when the number in them is positive as if its negative it naturally
has a "-" before it. I also need the cells to be formatted according
to another keycell whose decimal placing changes with another macro
from a button link.

So one cell is sitting there with 0.01 in it and it should say +0.01
(because in this example it is a positive number.

Thankyou.

 

[Rick Rothstein]

Does this macro do what you want?

Sub ChangeFormat()
Dim C As Range
Dim F As String
F = "+" & Range("ad22").NumberFormat & ";-" & Range("ad22").NumberFormat
For Each C In Range("U22:U26")
C.NumberFormat = F
Next
End Sub

--
Rick (MVP - Excel)

Providing the code was a good thing... the only thing you forgot to do is
tell us in words what it is supposed to be doing (so we don't have to
fight
our way through your logic). What do you have and what is this code
supposed
to be doing to it?

--
Rick (MVP - Excel)

I have this macro code:

Range("u22:u26").NumberFormat = _
Range("ad22").NumberFormat '(number format in ad22 is "0.00")

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

Set KeyCell = [q16]
Set AnswerCell = [u22:u26] 'set + - sign to cells

'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

If I rem out the first 2 lines with a ' it works and places a + or -
where it should, but if I perform the first 2 lines just before that
first, it doesent apply the + or -.

How can I make the two work together so it does +0.00 or -0.00?

Aaron.

Erm well I have a range of cells that I need to have a positive sign
on when the number in them is positive as if its negative it naturally
has a "-" before it. I also need the cells to be formatted according
to another keycell whose decimal placing changes with another macro
from a button link.

So one cell is sitting there with 0.01 in it and it should say +0.01
(because in this example it is a positive number.

Thankyou.

 

[Aaron]

Does this macro do what you want?

Sub ChangeFormat()
  Dim C As Range
  Dim F As String
  F = "+" & Range("ad22").NumberFormat & ";-" & Range("ad22").NumberFormat
  For Each C In Range("U22:U26")
    C.NumberFormat = F
  Next
End Sub

--
Rick (MVP - Excel)

Providing the code was a good thing... the only thing you forgot to do is
tell us in words what it is supposed to be doing (so we don't have to
fight
our way through your logic). What do you have and what is this code
supposed
to be doing to it?

news:f0073f36-8259-41db-be94-390e8a6c4cdf@a26g2000prf.googlegroups.com....

I have this macro code:
Range("u22:u26").NumberFormat = _
Range("ad22").NumberFormat '(numberformatin ad22 is "0.00")
Dim KeyCell As Range, AnswerCell As Range
Dim DP As Long
Dim kcFmt As String
Dim acFmt As String
Set KeyCell = [q16]
Set AnswerCell = [u22:u26] 'set + - sign to cells
'Get DP from Key Cell
kcFmt = KeyCell.NumberFormat
DP = Len(kcFmt) - InStr(1, kcFmt, ".") + 0 'add anumberhere to apply
more DP
'Add one decimal place to the KeyCellnumberformat
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 newformatto answer cell
AnswerCell.NumberFormat = acFmt
If I rem out the first 2 lines with a ' it works and places a + or -
where it should, but if I perform the first 2 lines just before that
first, it doesent apply the + or -.
How can I make the two work together so it does +0.00 or -0.00?
TIA,
Aaron.

Erm well I have a range of cells that I need to have a positive sign
on when thenumberin them is positive as if its negative it naturally
has a "-" before it. I also need the cells to be formatted according
to another keycell whose decimal placing changes with another macro
from a button link.

So one cell is sitting there with 0.01 in it and it should say +0.01
(because in this example it is a positivenumber.

Thankyou.

Yes it works great and I have incorporated it in my workbook to great
effect. Thank- you again.

Aaron.