Home 
Search 
Today's Posts 
#1




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




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




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) 