How can i find maximum values depending on different data

[stakar]

I have data like the following
in columns A and B in Worksheet 1

A B
5 00
6 00
1 01
3 01
2 01
4 20
1 20

The B column's data are text numbers (e.g 01, 0010, 10, 150 etc)
-----------------------------------------------------------
I have the following result in worksheet 2 unordered
(they are the unique values of previous column B)

C
00
20
01

--------------------------------------------------------
I want a D column with the above data's max values next to each value on column C in worksheet 2.
The source data are in the column A in worksheet 1.
Here's the result

C D
00 6
01 3
20 4


Im using this code , its fast and it is what i want....but

Private Sub CommandButton1_Click()
Dim RngB As Range
Dim RngC As Range
Dim Cll As Range

Set RngB = Range("B1", Range("B65536").End(xlUp))
Set RngC = Range("C1", Range("C65536").End(xlUp))

For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=MAX(IF(" & RngB.Address & "=" & Cll.Offset(, -1).Address & "," & RngB.Offset(, -1).Address & "))"
Cll.Value = Cll.Value
Next Cll
End Sub

But the problem is that i cant fix the code working with the two different worksheets because the A & B columns are in Worksheet 1, the column C is in worksheet 2 and so the new one D will be created in Worksheet 2

If anyone can help !!!!

Thanks in advance

[HalfAce]

Hi stakar,
I admit I haven't tested this, but it looks like you need to specify which sheet you're setting the variables RngB & RngC for, and then select sheet2 before performing the tests.
Try changing to this and see if it helps.

Private Sub CommandButton1_Click()
Dim RngB As Range
Dim RngC As Range
Dim Cll As Range

Set RngB = Sheets("Sheet1").Range("B1", Range("B65536").End(xlUp))
Set RngC = Sheets("Sheet1").Range("C1", Range("C65536").End(xlUp))

Sheets("Sheet2").Select
For Each Cll In RngC.Offset(, 1)
Cll.FormulaArray = "=MAX(IF(" & RngB.Address & "=" & Cll.Offset(, -1).Address & "," & RngB.Offset(, -1).Address & "))"
Cll.Value = Cll.Value
Next Cll
End Sub

Dan