Solved

# Find max date in an array of stored variables

Posted on 2013-12-10
249 Views
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))
``````
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
``````
``````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
``````
Thanks,
John
0
Question by:gabrielPennyback
• 2

LVL 29

Expert Comment

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

LVL 85

Accepted Solution

Rory Archibald earned 500 total points
ID: 39710917
No real need for an array since you are looping anyway:

``````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)
If EIS.Offset(0, 1).value > Dt then Dt = EIS.Offset(0, 1).Value
Next i
[W1] = Dt
End Sub
``````
0

LVL 85

Assisted Solution

Rory Archibald earned 500 total points
ID: 39710919
By the way, your last code can be simplified by using a Double array:
``````Sub demoArray1()
Dim Lng As Long
Dim Dt As Date
Dim myArray(0 To 3) As Double
myArray(0) = [D4]
myArray(1) = [D5]
myArray(2) = [D6]
myArray(3) = [D7]
Dt = WorksheetFunction.Max(myArray)
End Sub
``````
0

LVL 1

Author Closing Comment

ID: 39712502
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
``````

Good to hear from you!

John
0

## Featured Post

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.