asked on

Find max date in an array of stored variables

How do I apply the Array logic in the second macro to the first one. Basically, in my situation, there could be anywhere from 1 to NumSNs values in [LKP_SN] (NumSNs might be 3, for example). What I need to determine is the maximum value of the 2 or 3f occurrences of [V1] in in [LKP_SN].Offset(0, 1).

For example in this instance there are 3 occurrences of "myText" in [LKP_SN], each of which has a date in the cell immediately to its right. I need some code that will store the 3 numbers in my array, I want my variable Dt to be determined along the lines of

 Dt = WorksheetFunction.Max(myArray(0), myArray(1), myArray(2), myArray(3))

Open in new window

in the second macro. The last value of date discovered in my oversimplistic macro might not be the latest date. It wouldn't be a problem if I could store each of dates in a range on the worksheet, but i can't.

I'm not very good with ReDim and ReDim Preserve so I don't know how to apply it but please let me the simplest code necessary to achieve my goal.

Sub FindMaxDate()
Dim EIS As Range, NumSNs As Long, i As Long, Dt As Date, Dt2 As Date, Dt3 As Date
NumSNs = WorksheetFunction.CountIf([LKP_SN], [V1])
Set EIS = [LKP_SN].Find(what:=[V1].Value)
Dt = EIS.Offset(0, 1) 'On Error Resume Next
    For i = 2 To NumSNs
        Set EIS = [LKP_SN].Find(what:=[V1].Value, after:=EIS)
        Dt = EIS.Offset(0, 1)
    Next i
[W1] = Dt
End Sub

Open in new window

Sub demoArray1()
Dim Lng As Long
Dim Dt As Date
Dim myArray(0 To 3) As Date
myArray(0) = [D4]
myArray(1) = [D5]
myArray(2) = [D6]
myArray(3) = [D7]
Dt = WorksheetFunction.Max(myArray(0), myArray(1), myArray(2), myArray(3))
End Sub

Open in new window

Thanks,
John

Jacques Geday

thanks for your multiple examples. To understand better, could you post some sampe data that this code refers to ?
gowflow

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

Wow, thanks, Rory! Because I tend to get riveted on anything I can't figure out , I spent about 4 hours on this yesterday and I knew there had to be some really simple solution but I just couldn't channel it so to speak. I did figure out how to make the array work however, so it was worth it just to get some facility with a new tool, but your way is so much better. For what it's worth here's what I arrived at:

Sub FindMaxDateEIS()
Dim EIS As Range, iRow As Long, iCol As Long, NumSNs As Long, i As Long, Dt As Date, _
    Dt2 As Date, Dt3 As Date, x As String, Cstmr As Range
Set Cstmr = ActiveSheet.Shapes(Application.Caller).TopLeftCell
iCol = Cstmr.Column
For iRow = 4 To Cstmr.End(xlDown).Row
NumSNs = WorksheetFunction.CountIf([LKP_SN], Cells(iRow, iCol))
x = Cells(iRow, iCol)
Set EIS = [LKP_SN].Find(what:=x)
Dim DateArray(1 To 3) As Date
DateArray(1) = Dt
DateArray(2) = Dt
DateArray(3) = Dt
    For i = 1 To NumSNs
        Set EIS = [LKP_SN].Find(what:=x, after:=EIS)
        If EIS.Offset(0, 1) < 1 Then GoTo here
        DateArray(i) = EIS.Offset(0, 1)
here:
    Next i
Cells(iRow, iCol + 3) = WorksheetFunction.Max(DateArray(1), DateArray(2), DateArray(3))
Next iRow
End Sub

Open in new window

Good to hear from you!

John