Re: Normalising the values using VBA (algorithm given)

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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 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" <karokelip@xxxxxxxxx> wrote in message
news:1154807742.732892.275280@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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" <karokelip@xxxxxxxxx> wrote in message
news:1154803570.031205.122750@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.



.

Relevant Pages

  • Re: Normalising the values using VBA (algorithm given)
    ... Dim rng As Range ... Set rng = Selection ... conditional formatting and rest remain the ... Green if cell value is BETWEEN 0 and 5. ...
    (microsoft.public.excel.programming)
  • Re: change background color of cell as value changes
    ... if you don't use conditional formatting. ... colors, you would need to set up an algorithm, probably in the form of a case ... that would be called if any cell in Col A changes and if the value ... dynamically with other macro code that is already completed. ...
    (microsoft.public.excel.programming)
  • Re: Change Format of Active Cell
    ... cell to make it more easily visible at a quick glance. ...     'With Target.EntireRow ... to black (but only for the selection period)?- Hide quoted text - ... I am having problems with my Conditional formatting. ...
    (microsoft.public.excel.programming)
  • Re: Conditional Format an entire row
    ... Often it will be, but it's possible, depending on how the selection ... is" rather than "cell value is" for the whole row to be picked up. ... Highlight the entire block of data, the conditional formatting would ... Fred can easily see the date he is scheduled to volunteer. ...
    (microsoft.public.excel)
  • Re: Advanced "Conditional" formatting which changes by cell selection
    ... So the value of any cell in column A (except ... Row 1,2,3,4 and 5 are heading rows. ... To do that you could use conditional formatting using the formula ... current selection is, or what it contains. ...
    (microsoft.public.excel.programming)