Find Last Occurance of Given Month & Year in Range

[talt0612]

Hi all- I'm stuck on a problem. For a given date, how do I find the last occurance of the given dates' month and year in a range?

Here are some more details:
A1 =a random date in the format "MM/DD/YYYY".
Column B = range of dates from 1/1/2009 - today in format "MM/DD/YYYY"
Column C = a transaction value corresponding to the date in B.

The dates are in column B are in chronological order, but not all dates are listed. So for instance B1 might be 1/1/2009, B2 is 1/2/2009, but B3 is 1/7/2009 because there were no transactions from 1/3/2009 to 1/6/2009.

So if A1 is 1/1/2009, I need to look in Column B for the last transaction date in the month of Jan 2009 and return the value in column C.

Any thoughts on how I can do this in VBA. I'm using Excel 2007.

Thanks very much in advance.

 

[MickG]

Try this for result in "D1":-

[COLOR=navy]Sub[/COLOR] MG25Apr33
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dt [COLOR=navy]As[/COLOR] Date
Dt = Range("A1")
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Month(Dn) = Month(Dt) + 1 And Year(Dn) = Year(Dt) [COLOR=navy]Then[/COLOR]
        Range("D1") = Dn.Offset(-1, 1)
        [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[talt0612]

Try this for result in "D1":-

[COLOR=navy]Sub[/COLOR] MG25Apr33
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dt [COLOR=navy]As[/COLOR] Date
Dt = Range("A1")
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    [COLOR=navy]If[/COLOR] Month(Dn) = Month(Dt) + 1 And Year(Dn) = Year(Dt) [COLOR=navy]Then[/COLOR]
        Range("D1") = Dn.Offset(-1, 1)
        [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

Thanks Mick. I was hoping to avoid a loop if possible, but I think this will work for my purposes. I just added a line to handle the case where the date/year don't exist:

Else
Range("D1").Value = "No Transactions Found"

Thanks again.

 

[talt0612]

Thanks Mick. I was hoping to avoid a loop if possible, but I think this will work for my purposes. I just added a line to handle the case where the date/year don't exist:

Thanks again.

Hey Mick- I have one more issue. If the random date =today and the last transaction was in this month & year, the code will not return any value because there is no data for the next month yet.

Any idea how to get around this? i.e. if A1=today, then show the most recent transaction?

 

[Jonmo1]

Formula method...

=LOOKUP(EDATE(A1-DAY(A1),1),B:C)

EDATE requires the analysis toolpack from Tools - Addins
This is installed by default on XL2007 or higher..

 

[talt0612]

Formula method...

=LOOKUP(EDATE(A1-DAY(A1),1),B:C)

EDATE requires the analysis toolpack from Tools - Addins
This is installed by default on XL2007 or higher..

Thanks jonmo1. Unfortunately I've run in to another issue. I need to search from the bottom of the range because a given date can appear more than once and I need to find the last occurance.

I very much appreciate any help you can offer.

 

[Jonmo1]

It's working that way for me..

The formula isn't looking for "the given date"
It's taking the given date, and creating a new date that is the last day of the given date's month/year.
It then looks for that new date.

So if the given date is 2/7/2012, then it converts that to 2/29/2012
It then looks for that date.


Can you post a sample set of data that returns the wrong value?

 

[talt0612]

It's working that way for me..

The formula isn't looking for "the given date"
It's taking the given date, and creating a new date that is the last day of the given date's month/year.
It then looks for that new date.

So if the given date is 2/7/2012, then it converts that to 2/29/2012
It then looks for that date.


Can you post a sample set of data that returns the wrong value?

A1=3/3/12
B1= 3/1/12
B2:B19 = [R0C-1]+3
C1=Bond
C2=cash
C3=sub (repeat this sequence through C19)
D1 = LOOKUP(EDATE(A1-DAY(A1),1),B:C)

D1 returns "Bond" when it should return "cash"

The issue appears to be because "A1-DAY(A1)" returns 2/29/12 and then EDATE moves the month forward to 3/29/12. Unfortunately in this case there is a value on 3/31/12 ("cash").

Any thoughts? And do you know a way I can do this in VBA as part of a bigger macro.

Thanks very much.

 

[Jonmo1]

I don't follow what this means
B2:B19 = [R0C-1]+3

 

[talt0612]

I don't follow what this means
B2:B19 = [R0C-1]+3

sorry, reverted to R1C1 here....B2= B1+3, B3=B2+3....

What if I modify the formula to :

=LOOKUP(EOMONTH(A1,0),B:C)

This seems to work even when the last two dates in the range are the same (i.e. today), but I'm not sure how, why.