=INDIRECT("B"&MATCH(MAX(A1:A9),A1:A9,0))
A few words of thanks would be greatly appreciated. Add comment
5907 users have said thank you to us this month
Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) x = WorksheetFunction.Max(r) Set rmax = Cells(WorksheetFunction.Match(x, r, 0), "b") Cells(k, "C").Value = rmax Next k End Sub
Sub test() Dim j As Integer, k As Integer, r As Range, x As Double, rmax As Range Worksheets("sheet1").Activate Columns("C:C").Delete j = Range("A1").End(xlDown).Row For k = 1 To j Step 4 Set r = Range(Cells(k, 1), Cells(k + 3, 1)) 'msgbox r.Address x = WorksheetFunction.Max(r) 'msgbox x Set rmax = Cells(WorksheetFunction.Match(x, r, 0) + k - 1, "b") 'the modification is in the above line 'msgbox rmax.Address Cells(k, "C").Value = rmax Next k End Sub
DON'T MISS
i need some more help form u.
actually i have a set of values in a column and i have 2 compare only by four,four cells and find the max & the value next to that cell.
i.e
@ my excel i have
A B C
10 4 6
20 6
15 7
05 9
45 1 3
35 5
52 3
20 4 ..... like that goes on..
I need the values in column "C" as i specified..