Finding maximum between dates with a suffixed number

[subbby]

I have some inputs in Column A that are in the format DATE_Package # . Date is in YYYY-MM-DD format.

Example

2016-10-24_Package 24
2016-10-24_Package 25
2016-10_31_Package 26
2016-11-01_Package 27
2016-11-01_Package 28

All these values are in sequential order.

I want my program to find the maximum of the above. Meaning = the most recent and message what my next pacakge number will be. So when macro button is clicked, it should pick todays's date and add suffix "Package 29" and thereby should read as 2016-11-02_Package 29

Used a similar program for 3 years where the very same column was just numerical. Example, 1,2,3,4..... . So I used a code to find the Maximum and then add a count to it and messagebox the new one. Here is the code I used initially

Please Login or Register  to view this content.

I tried to use this

Please Login or Register  to view this content.

but it ends up giving the same value .

So how do i improvise

Please Login or Register  to view this content.

in the the above code so that it just adds up the number

[subbby]

I am trying to use

Please Login or Register  to view this content.

But with that it takes the last character only. But I would want it to consider all characters after the word "package" and the space. To make sure it works when the previous value is
2016-11-01_Package 1001

[bakerman2]

Let's start with this.

Please Login or Register  to view this content.

[subbby]

can you explain your code?

What does ReDim , UBound do ?

How do I define the current region ?

I copied your code and tried to run it but instead gave me an error. I am not so well versed with excel. Would appreciate it if you can explain it a bit.

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[subbby]

Thanks for the help John. This is very much tied up to another thread of mine you just responded to.

I modified my code to

Please Login or Register  to view this content.

and it works fine.

However, I am going to try your approach shortly

Thanks,

Subbby

[JOHN H. DAVIS]

You're welcome. Hope you get it resolved.

[bakerman2]

The only problem is that John's code won't work with package numbers larger then 999.
Following does.

Please Login or Register  to view this content.

@ JOHN
Thanks for giving good start.

[JOHN H. DAVIS]

@ bakerman2 - Oh Yeah, I didn't account for POST #2. Thanks for catching that and offering a reliable solution.