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
Solved

Find max date in an array of stored variables

Posted on 2013-12-10
4
254 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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;…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

765 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