Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
negative numbers | Excel Discussion (Misc queries) | |||
convert negative numbers to positive numbers and vice versa | Excel Worksheet Functions | |||
Add sequences of positive then negative numbers | Excel Discussion (Misc queries) | |||
How can I change positive numbers to negative, i.e. change 50 to - | Excel Discussion (Misc queries) | |||
Converting negative numbers in a range of cells to zero | Excel Discussion (Misc queries) |