Remember Me?

#1
September 11th 06, 11:53 PM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 53
Preserve negative numbers in following macro

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

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

Cheers,

Aaron.

#2
September 12th 06, 12:39 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Jul 2006 Posts: 2,062
Preserve negative numbers in following macro

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(P3:P6))MAX(P3:P6),MIN(P3:P6),MAX(P3:P 6))

Change the range to wherever your data is.

"Slashman" wrote:

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

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

Cheers,

Aaron.

#3
September 12th 06, 12:57 AM posted to microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 53
Preserve negative numbers in following macro

Hi JMB,

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

Thanks alot.

Aaron.
JMB wrote:
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(P3:P6))MAX(P3:P6),MIN(P3:P6),MAX(P3:P 6))

Change the range to wherever your data is.

"Slashman" wrote:

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

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

Cheers,

Aaron.

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post jaxstraww Excel Discussion (Misc queries) 6 September 1st 06 09:40 PM bill gras Excel Worksheet Functions 4 December 7th 05 01:39 AM judoist Excel Discussion (Misc queries) 6 November 26th 05 05:51 AM godwingi Excel Discussion (Misc queries) 5 February 28th 05 05:41 PM Dede Excel Discussion (Misc queries) 3 January 14th 05 06:23 PM

All times are GMT +1. The time now is 11:29 PM.