Max VBA Function??

[robfo0]

Hi everyone,

I have 4 variables (dates in column B) in a worksheet. I am trying to find the most recent date out of the 4 and return the cell value to the left of it (value in column A).

Before, I only had 3 variables and was using a nested if-then-else, which was working pretty good, but now i have another variable, and a nested 4 variable if then else is a lot harder to handle than 3. I was wondering if there is another way to accomlish what I am trying to do.

Any help is greatly appreciated!

edit: I was previously doing this in VBA during a routine that is run. I would like to keep it in VBA so that I do not have to go back and edit files, which is where the data comes from (long story). So I would like to keep it in VBA if possible
This message was edited by robfo0 on 2002-08-26 17:15

 

[Legacy 98055]

Hi Rob
The Max function is available in VBA.
Search VBA help for "List of Worksheet Functions Available to Visual Basic"
Tom

 

[Juan Pablo González]

Continouing with Tom's reply, you can just use:

MaxValue = Application.Max(1,2,3)

 

[Jay Petrulis]

Hi,

Here is one way to do what you wish in VBA...<pre>Sub test()
Dim Rng As Range, FoundCell



With ActiveSheet
Set Rng = Intersect(.UsedRange, .Columns(2))
If Not Rng Is Nothing Then
FoundCell = _
.Cells(Application.Match(Application.Max(Rng), Rng, 0), Rng.Column - 1)
End If
End With


End Sub</pre>

Also, with your data in A1:B4, for instance, here is one worksheet method

=INDEX(A1:B4,MATCH(MAX(B1:B4),B1:B4,0),1)

_________________
Bye,
Jay

EDIT: Sorry about the worksheet answer -- completely missed the VBA only request.
This message was edited by Jay Petrulis on 2002-08-26 19:58

 

[robfo0]

Thanks very much Jay, works great, much better than a 10 paragraph if then else statement

 

[robfo0]

Ok, Here is another question, i know i didnt state this at first, but the 4 cells that i am testing for are not all grouped together, and are in column A along with 400 other rows of data.... How would i modify this code to only check these 4 cells, say for example they are A1, A25, A50, and A75.

Thanks again

 

[Jay Petrulis]

Hi,

That's a big step to leave out, don't you think? Also, your test values are in column B with the return values in Column A, right?

Here is something that worked for me...<pre>Sub test()
Dim Rng As Range, FoundCell
Dim CellsArray, FindMax, x


With ActiveSheet
Set Rng = Range("A1:B1,A10:B10,A50:B50,A75:B75")

For x = 1 To Rng.Areas.Count
CellsArray = Rng.Areas(x).Value
If x = 1 Then
FindMax = CellsArray(1, 2)
FoundCell = CellsArray(1, 1)
Else
If CellsArray(1, 2) > FindMax Then
FindMax = CellsArray(1, 2)
FoundCell = CellsArray(1, 1)
End If
End If
Next x

End With
MsgBox FoundCell
End Sub</pre>

If the cells to test are changing, then consider naming the discontinuous range and working as above from there.


_________________
Bye,
Jay
This message was edited by Jay Petrulis on 2002-08-27 15:24

 

[robfo0]

Thanks so much Jay, the cells *shouldnt* be changing, so this should work great. I left out that step because I figured if someone helped me out that far, i could figure out the rest, so thanks for the extra help

 

[Jay Petrulis]

On 2002-08-27 15:31, robfo0 wrote:
Thanks so much Jay, the cells *shouldnt* be changing, so this should work great. I left out that step because I figured if someone helped me out that far, i could figure out the rest, so thanks for the extra help

No problem. Just be aware that when you are working with discontinuous ranges, it can get a bit tricky.

 

[Juan Pablo González]

Jay, you can use the For Each ... Next structure... to avoid the "trickiness" :wink: (And why do you use With ActiveSheet ? you don't use it there anywhere !)

<pre>Sub test()
Dim Rng As Range, Cll As Range
Set Rng = Range("A1:B1,A10:B10,A50:B50,A75:B75")
For Each Cll In Rng
MsgBox Cll.Value
Next Cll
End Sub</pre>