Preserve negative numbers in following macro

[Slashman]

Hi,

Ron gave me this Macro which does exactly what I want, except for one
small point I have just noticed.

////////////////////////////////////////////////////////////////////

Sub Progressive_Error()

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


Set KeyCell = [q16]
Set AnswerCell = [r18]
Set SuffixCell = [r14]


'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"


'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) & Suffix


'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix


acFmt = "+" & acFmt & ";-" & acFmt & ";0"


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


End Sub

///////////////////////////////////////////////////////////////////////////////////////////



In the following cells are the numbers I want to find the largest error
from.

0
10
15
-5
-3
-18

In the above numbers, 15 is the largest number, but -18 is the highest
error.

I use the MAXA function to remove the signs (as this is the only way I
know how to) and then I have 18 as my Answercell. The macro above, then
assigns a + sign to this Answercell value all the time, (quite rightly
because it is a positive number) but I have realised now that I need it
to recognise it as a negative number (as it sometimes is for the
highest error) and then the Answercell will reflect this as either a -
or + prefix.

Currently the macro formats the cell as +18.00g as Suffixcell is "g"
and Keycell is "0.00" DP but I need it to format it is -18.00g


Any advice greatly appreciated.

Cheers,

Aaron.

 

[JMB]

I assume you are using a formula in cell R18. The formatting macro worked
fine for me, you have to modify your formula to return -18. I used

=IF(ABS(MIN(P36))>MAX(P36),MIN(P36),MAX(P36))

Change the range to wherever your data is.

 

[Slashman]

Hi JMB,

Thats the solution I wanted. It works 100% how I needed it to.

Thanks alot.

Aaron.

I assume you are using a formula in cell R18. The formatting macro worked
fine for me, you have to modify your formula to return -18. I used

=IF(ABS(MIN(P36))>MAX(P36),MIN(P36),MAX(P36))

Change the range to wherever your data is.

Hi,

Ron gave me this Macro which does exactly what I want, except for one
small point I have just noticed.

////////////////////////////////////////////////////////////////////

Sub Progressive_Error()

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


Set KeyCell = [q16]
Set AnswerCell = [r18]
Set SuffixCell = [r14]


'Note the quotes within the quotes.
Suffix = """" & SuffixCell.Text & """"


'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) & Suffix


'special case of kcFmt is "0"
If kcFmt = "0" Then acFmt = "0.0" & Suffix


acFmt = "+" & acFmt & ";-" & acFmt & ";0"


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


End Sub

///////////////////////////////////////////////////////////////////////////////////////////



In the following cells are the numbers I want to find the largest error
from.

0
10
15
-5
-3
-18

In the above numbers, 15 is the largest number, but -18 is the highest
error.

I use the MAXA function to remove the signs (as this is the only way I
know how to) and then I have 18 as my Answercell. The macro above, then
assigns a + sign to this Answercell value all the time, (quite rightly
because it is a positive number) but I have realised now that I need it
to recognise it as a negative number (as it sometimes is for the
highest error) and then the Answercell will reflect this as either a -
or + prefix.

Currently the macro formats the cell as +18.00g as Suffixcell is "g"
and Keycell is "0.00" DP but I need it to format it is -18.00g


Any advice greatly appreciated.

Cheers,

Aaron.