Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range help
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range help
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 -- Dan On Dec 18, 3:22 pm, millwalll wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Range help
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.M atch( _ Application.WorksheetFunction.Max(Range("K:K")), Range("K:K"), 0)) debug.print v -- Regards, Sébastien <http://www.ondemandanalysis.com "millwalll" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
Excel Addin:Setting the range to the Excel.Range object range prop | Excel Worksheet Functions | |||
Range Question / error 1004: method Range of object Worksheet has failed | Excel Programming | |||
Range.Find returns cell outside of range when range set to single cell | Excel Programming | |||
how to? set my range= my UDF argument (range vs. value in range) [advanced?] | Excel Programming |