Normalising the values using VBA (algorithm given)

[Thulasiram]

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.

 

[Bob Phillips]

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]

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]

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]

Mr.Bob,

code works perfectly weel and solved my problem completely. fantastic!

Thanks a lot.

Regards,
-Thulasiram

 

[Thulasiram]

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 are: results is the conditional
formatting and results displayed in the same page.

Conditions are:
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]

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)

 

[Dana DeLouis]

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

 

[Thulasiram]

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.

 

[Thulasiram]

Dana,

That was a nice suggestion too.

Thanks,
Thulasiram.

 

[Bob Phillips]

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]

That was perfect Mr.Bob. Thanks a million.

Best,
Thulasiram