Solved

Find max date in an array of stored variables

Posted on 2013-12-10
4
249 Views
Last Modified: 2013-12-11
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
0
Comment
Question by:gabrielPennyback
  • 2
4 Comments
 
LVL 29

Expert Comment

by:gowflow
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

by:
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

Open in new window

0
 
LVL 85

Assisted Solution

by:Rory Archibald
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

Open in new window

0
 
LVL 1

Author Closing Comment

by:gabrielPennyback
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

Open in new window



Good to hear from you!

John
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now