Range help

[millwalll]

Hi all I have some Data in Coloum K I have Payment amount. I want to Search
this and find the higest amount. Then whatever filed the higest amount is in
go across to coloum c and take the value of that field I have some idea how
it may go but just wanted to get better idear any help be great thanks

 

[Dan R.]

Try this:

Sub test()
Dim i As Range, rng As Range
Dim lRow As Long, mx As Double
lRow = Range("K65536").End(xlUp).Row
Set rng = Range(Cells(1, 11), Cells(lRow, 11))
mx = Application.WorksheetFunction.Max(rng)
For Each i In rng
If i = mx Then
Exit For
End If
Next i
MsgBox i.Offset(, -8)
End Sub

 

[sebastienm]

Hi,
If the value of the C cell is all you need:

A1: =MAX(K:K) ''' get Max value
A2: =MATCH(A1,K:K,0) ''' find cell containing Max value
A3: =INDEX(C:C,A2,1) '''' get value in cells C

Now, as one worksheet formula:
=INDEX(C:C, MATCH( MAX(K:K) ,K:K,0) , 1)
Or if you need it within vba:
dim v
v = Range("C:C").Cells(Application.WorksheetFunction.Match( _
Application.WorksheetFunction.Max(Range("K:K")), Range("K:K"), 0))
debug.print v