Enter a date, find the period..?

[towners]

Hi all,

I'me entering a date and want to find the period from a set of data. Can anyone help?

ColA (period number) Col B (from date) Col C (to date)

Period 1; 3/12/10; 31/12/10
Period 2; 3/1/11; 28/1/11
Period 3; 3/2/11; 28/2/11

So I want to put some code into my userform VBA that takes the date and find the period to then add to my worksheet.

i.e. date = 5/12/10, Period = 1

Any help gratefully received...

Thanks

Paul

 

[towners]

Hi, thanks for the replies.

The challenge I have is that I cannot guarantee the from/to date buckets. What I think I need is some kind of lookup that tests to see which dates the entered date falls between and return the corresponding period number. Each year the user will update the period table with new dates so I need to always look up the value.

 

[MickG]

The previous code is modified to a Worksheet_Change Event code:-
Right click sheet tab , select "View Code" , VB window appears, Paste code into VB window.
Close VB window.
Enter date in cell "D1"
Result appears in "E1".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[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
[COLOR="Navy"]Dim[/COLOR] fd [COLOR="Navy"]As[/COLOR] Boolean
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "D1" [COLOR="Navy"]Then[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
   [COLOR="Navy"]If[/COLOR] CDate(Dn(, 2)) <= Range("D1") And CDate(Dn(, 3)) >= Range("D1") [COLOR="Navy"]Then[/COLOR]
        Range("E1") = Dn
        fd = True
         [COLOR="Navy"]Exit[/COLOR] For
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]If[/COLOR] fd = False [COLOR="Navy"]Then[/COLOR] Range("E1") = "No Match Found"
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[towners]

Mick, I reckon that will do it - thanks. Was just experimenting with INDEX MATCH to try that way but was wondering about worksheet change event...

Just one question you might be able to help me with though, my period lookup table is on a different worksheet with the columns in named ranges (I dynamically size the range for graphing and reporting). I'm attempting and failing to point to these ranges, I thought this was the correct syntax but I'm obviously doing something wrong. Can you help?

Set Rng = Range("Periods_PeriodNumber")

Cheers

Paul

 

[MickG]

Hi, Try this:-
This code assumes the Named range (on another sheet) is the range with the "Periods" in it and the dates are in columns offset 1 and 2 to its right.

Private [COLOR=navy]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR=navy]As[/COLOR] Range)
[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
[COLOR=navy]Dim[/COLOR] fd [COLOR=navy]As[/COLOR] Boolean
[COLOR=navy]Dim[/COLOR] ws [COLOR=navy]As[/COLOR] Worksheet
[COLOR=navy]If[/COLOR] Target.Address(0, 0) = "D1" [COLOR=navy]Then[/COLOR]
[COLOR=navy]Set[/COLOR] rng = ActiveWorkbook.Names("Periods_PeriodNumber").RefersToRange
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] rng
   [COLOR=navy]If[/COLOR] CDate(Dn(, 2)) <= Range("D1") And CDate(Dn(, 3)) >= Range("D1") [COLOR=navy]Then[/COLOR]
        Range("E1") = Dn
        fd = True
         [COLOR=navy]Exit[/COLOR] For
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]If[/COLOR] fd = False [COLOR=navy]Then[/COLOR] Range("E1") = "No Match Found"
[COLOR=navy]End[/COLOR] [COLOR=navy]If[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[towners]

That's properly nailed it. Thanks, you've been a big help.