Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Hello people,
I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Mr. Bob,
Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Mr.Bob,
code works perfectly weel and solved my problem completely. fantastic! Thanks a lot. Regards, -Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Mr. Bob,
Two questions. All of them requires slight modification of the present algorithm. I have been trying to manipulate ur code to fit my different conditions. But I landed into trouble. Case 1: Code to normalise the algorithm and display the results in the same worksheet - YOU HAVE GIVEN THE CODE in your previous reply Case 2: Code to normalise the algorithm, display the results in the next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in your previous reply **** I would like to have the code for case 2 but "results displayed in the same page". Algorithm, conditional formatting and rest remain the same. The only change is that results should be in the same page. **** I would like to have the code for case 2 but " 3 conditions for conditional formatting". Algorithm, conditional formatting and rest remain the same. The two changes a results is the conditional formatting and results displayed in the same page. Conditions a Yellow if cell value is zero. Green if cell value is BETWEEN 0 and 5. Red if cell value is greater than 5. (I tried few if.. else.. conditions in ur code for case 2.. but landed in unpredictable error). Please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Is this what you want?
Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Mr. Bob, Two questions. All of them requires slight modification of the present algorithm. I have been trying to manipulate ur code to fit my different conditions. But I landed into trouble. Case 1: Code to normalise the algorithm and display the results in the same worksheet - YOU HAVE GIVEN THE CODE in your previous reply Case 2: Code to normalise the algorithm, display the results in the next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in your previous reply **** I would like to have the code for case 2 but "results displayed in the same page". Algorithm, conditional formatting and rest remain the same. The only change is that results should be in the same page. **** I would like to have the code for case 2 but " 3 conditions for conditional formatting". Algorithm, conditional formatting and rest remain the same. The two changes a results is the conditional formatting and results displayed in the same page. Conditions a Yellow if cell value is zero. Green if cell value is BETWEEN 0 and 5. Red if cell value is greater than 5. (I tried few if.. else.. conditions in ur code for case 2.. but landed in unpredictable error). Please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
A very small idea may be to calculate the common number once. Perhaps
something like this. Dim M As Double Set rng = Selection M = 10 / WorksheetFunction.Max(rng) Then later in the loop: .Value = M * rng.Cells(i, j).Value -- Dana DeLouis <snip |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
That was what I perfectly wanted Mr.Bob. The results are shown diagonal
to the existing workspace. Is there any possibility to show the bring just below the workspace? Thanks for the help rendered. Best, Thulasiram. Bob Phillips wrote: Is this what you want? Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Mr. Bob, Two questions. All of them requires slight modification of the present algorithm. I have been trying to manipulate ur code to fit my different conditions. But I landed into trouble. Case 1: Code to normalise the algorithm and display the results in the same worksheet - YOU HAVE GIVEN THE CODE in your previous reply Case 2: Code to normalise the algorithm, display the results in the next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in your previous reply **** I would like to have the code for case 2 but "results displayed in the same page". Algorithm, conditional formatting and rest remain the same. The only change is that results should be in the same page. **** I would like to have the code for case 2 but " 3 conditions for conditional formatting". Algorithm, conditional formatting and rest remain the same. The two changes a results is the conditional formatting and results displayed in the same page. Conditions a Yellow if cell value is zero. Green if cell value is BETWEEN 0 and 5. Red if cell value is greater than 5. (I tried few if.. else.. conditions in ur code for case 2.. but landed in unpredictable error). Please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Dana,
That was a nice suggestion too. Thanks, Thulasiram. Dana DeLouis wrote: A very small idea may be to calculate the common number once. Perhaps something like this. Dim M As Double Set rng = Selection M = 10 / WorksheetFunction.Max(rng) Then later in the loop: .Value = M * rng.Cells(i, j).Value -- Dana DeLouis <snip |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
Sub Normalise()
Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... That was what I perfectly wanted Mr.Bob. The results are shown diagonal to the existing workspace. Is there any possibility to show the bring just below the workspace? Thanks for the help rendered. Best, Thulasiram. Bob Phillips wrote: Is this what you want? Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Mr. Bob, Two questions. All of them requires slight modification of the present algorithm. I have been trying to manipulate ur code to fit my different conditions. But I landed into trouble. Case 1: Code to normalise the algorithm and display the results in the same worksheet - YOU HAVE GIVEN THE CODE in your previous reply Case 2: Code to normalise the algorithm, display the results in the next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in your previous reply **** I would like to have the code for case 2 but "results displayed in the same page". Algorithm, conditional formatting and rest remain the same. The only change is that results should be in the same page. **** I would like to have the code for case 2 but " 3 conditions for conditional formatting". Algorithm, conditional formatting and rest remain the same. The two changes a results is the conditional formatting and results displayed in the same page. Conditions a Yellow if cell value is zero. Green if cell value is BETWEEN 0 and 5. Red if cell value is greater than 5. (I tried few if.. else.. conditions in ur code for case 2.. but landed in unpredictable error). Please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Normalising the values using VBA (algorithm given)
That was perfect Mr.Bob. Thanks a million.
Best, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... That was what I perfectly wanted Mr.Bob. The results are shown diagonal to the existing workspace. Is there any possibility to show the bring just below the workspace? Thanks for the help rendered. Best, Thulasiram. Bob Phillips wrote: Is this what you want? Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 ElseIf .Value <= 5 Then .Interior.ColorIndex = 10 Else .Interior.ColorIndex = 3 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Mr. Bob, Two questions. All of them requires slight modification of the present algorithm. I have been trying to manipulate ur code to fit my different conditions. But I landed into trouble. Case 1: Code to normalise the algorithm and display the results in the same worksheet - YOU HAVE GIVEN THE CODE in your previous reply Case 2: Code to normalise the algorithm, display the results in the next worsheet with conditional formatting - YOU HAVE GIVEN THE CODE in your previous reply **** I would like to have the code for case 2 but "results displayed in the same page". Algorithm, conditional formatting and rest remain the same. The only change is that results should be in the same page. **** I would like to have the code for case 2 but " 3 conditions for conditional formatting". Algorithm, conditional formatting and rest remain the same. The two changes a results is the conditional formatting and results displayed in the same page. Conditions a Yellow if cell value is zero. Green if cell value is BETWEEN 0 and 5. Red if cell value is greater than 5. (I tried few if.. else.. conditions in ur code for case 2.. but landed in unpredictable error). Please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count With Worksheets("Sheet2").Range("A1").Cells(i, j) .Value = 10 * rng.Cells(i, j).Value / Application.Max(rng) If .Value = 0 Then .Interior.ColorIndex = 6 Else .Interior.ColorIndex = 10 End If .Value = Round(.Value, 0) End With Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message ups.com... Mr. Bob, Thanks a lot for your reply. Surprised to see such a compact code. Couple of questions more. This should end my questions related to this topic. 1. How to move the values to another worksheet... i.e. move the resultant values to another sheet in the same workbook? currently the code given by you pastes the resultant value in the same worksheet. 2. Is it possible to do conditional formatting in the given code... (currently i use a recoded macro and it is not generic with respect to user selection) I would like to have a conditional formatting like: if the value is equal to 0, then cell to colored with yellow if the value is greater than 0 (includes values that are rounded to zero i.e values like 0.34 represented as 0 while rounding), then cell to colored with green. please help. Thanks, Thulasiram Bob Phillips wrote: Sub Normalise() Dim i As Long, j As Long Dim rng As Range Set rng = Selection For i = 1 To rng.Rows.Count For j = 1 To rng.Columns.Count rng.Cells(rng.Rows.Count + i, rng.Columns.Count + j).Value = _ Round(10 * rng.Cells(i, j).Value / Application.Max(rng), 0) Next j Next i End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Thulasiram" wrote in message oups.com... Hello people, I would like to normalize the values selected by the user. Algorithm for it: 1. from the selected values of the user, find the largest value 2. for each selected cell value, divide that cells value by the maximum value and muliply it by 10. for example, when written as a formula it may be like this =10*B2/MAX($B$2:$BR$25); =10*D5/MAX($B$2:$BR$25); =10*C3/MAX($B$2:$BR$25) etc... 3. Round the values. VBA code that i currently use for rounding is Dim Rng As Range Set Rng = ActiveCell Rng.Value = Application.WorksheetFunction. _ Round(Rng.Value, 0) unfortunately this code roundly only one cell but not all the selected cells. please rectify this issue too. 4. paste the resulting rounded values in a separate place in the same worksheet. i.e not to overlap with the selected area. ALL the FOUR steps come under the tag NORMALISING. i have a command button called as "NORMALISE". i would like to know the VBA code(that covers the four steps in the slgorithm) to be written in the click event. please help. Thanks for all help to be rendered. Regards, Thulasiram. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Algorithm Problem | Excel Programming | |||
Algorithm Challenge | Excel Programming | |||
algorithm creation | Excel Programming | |||
help with algorithm | Excel Programming | |||
Need help with algorithm | Excel Programming |