Solved

Find max date in an array of stored variables

Posted on 2013-12-10
4
257 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 30

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

707 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