Finding Highest Number and Selecting Adjacent Cell
Posted by Casey on February 06, 2002 11:52 AM
Hello,
Im trying to create a macro in VB which given three columns, will search the first twenty rows of the third column for the highest number and then report the value to the left of it( the value of the second column) I know there is a function called MAX under statistics, but I dont know if that will help.
Thanks for any help you can give me
Casey
Posted by Damon Ostrander on February 06, 2002 1:13 PM
Hi Casey,
There may be a built-in worksheet function way to do this, but here is a UDF that makes it easy:
Function MaxOffset(InRange As Range, OffsetCol) As Variant
' searches the range InRange for its maximum value, then
' returns the value offset by OffsetCol columns. If OffsetCol
' is zero the maximum value itself is returned, if -1 the
' value in the column just to the left, and +1 the value in
' the column just to the right.
' Usage: =MAXOFFSET(G1:G2000,-1)
' (returns the value in column F that is next to the maximum
' value in the range G1:G2000)
Dim MaxCel As Range
Dim MaxVal As Variant
Dim C As Range
Set MaxCel = Nothing
For Each C In InRange
If MaxCel Is Nothing Then
Set MaxCel = C
MaxVal = C.Value
ElseIf C.Value > MaxVal Then
Set MaxCel = C
MaxVal = C.Value
End If
Next C
MaxOffset = MaxCel.Offset(0, OffsetCol)
End Function
If its usage is not self-explanatory, let me know and I'll clarify.
Damon
Posted by CASEY on February 06, 2002 1:47 PM
Thanks for your help,
I used alt-f11 to open my personal folder then pasted it in, but it doesnt seem to be working.
ive used it as =maxoffset(B1:B16,-1)in the c column. The code seems to be exactly what I want, but I dont know if im applying it right.
thanks
Casey
Set MaxCel = Nothing For Each C In InRange If MaxCel Is Nothing Then Set MaxCel = C MaxVal = C.Value ElseIf C.Value > MaxVal Then Set MaxCel = C MaxVal = C.Value End If Next C MaxOffset = MaxCel.Offset(0, OffsetCol)
Posted by Mark W. on February 06, 2002 2:55 PM
The built-in way...
ERR
Posted by IML on February 06, 2002 4:26 PM
Just curious
Why offset as opposed to
=INDEX(F:G,MATCH(MAX(G:G),G:G,1),1)?
Any advatages or just dealer's choice?
Thanks,
Ian
Set MaxCel = Nothing For Each C In InRange If MaxCel Is Nothing Then Set MaxCel = C MaxVal = C.Value ElseIf C.Value > MaxVal Then Set MaxCel = C MaxVal = C.Value End If Next C MaxOffset = MaxCel.Offset(0, OffsetCol)
Posted by Mark W. on February 06, 2002 5:01 PM
It's largely a matter of style, but...
I find OFFSET to be more flexible, and easier to modify...
Suppose now I want the adjacent value and the
two that follow...
{=OFFSET(G1,MATCH(MAX(G:G),G:G,0)-1,-1,3,1)}
...try that with INDEX!
Set MaxCel = Nothing For Each C In InRange If MaxCel Is Nothing Then Set MaxCel = C MaxVal = C.Value ElseIf C.Value > MaxVal Then Set MaxCel = C MaxVal = C.Value End If Next C MaxOffset = MaxCel.Offset(0, OffsetCol)
Posted by Damon Ostrander on February 08, 2002 8:43 AM
Hi again Casey,
By now you've probably decided to use the built-in function approach suggested by others (I would have), but for future reference it's really
quite easy to install a UDF or macro. Just follow these steps:
1) Go to the Visual Basic Editor (VBE). Do this from Tools ->
Macro -> Visual Basic Editor
2) In the VBE create a new Macro Module: Insert -> Module.
An empty code window pane will appear.
3) Paste the code into this window. The macro or function is
now available for use from Excel. If it is a Function type
macro you can immediately use it as an Excel function. If
it is a Sub (subroutine) type macro you can run it from
the Excel Tools -> Macro menu.
Happy computing.
Damon
Set MaxCel = Nothing For Each C In InRange If MaxCel Is Nothing Then Set MaxCel = C MaxVal = C.Value ElseIf C.Value > MaxVal Then Set MaxCel = C MaxVal = C.Value End If Next C MaxOffset = MaxCel.Offset(0, OffsetCol)
