PDA

View Full Version : date range and return value



preseb
10-04-2012, 05:12 AM
I need help with a section of a macro that I am putting together. It involves date ranges and bringing back the assigned month.
On one worksheet, I have a list of dates that cover over a year’s time. This sheet is labeled “FY2013”. In another worksheet (labeled “Macro”) I have setup three columns. The month, start date and end date.
The reason is that if I am looking at the month of July, we are not identifying it as 7/1-7-31. It may be 7/1 – 8/6.



WS FY2013 Returned Value WS Macro – Month WS Macro – Start WS Macro–End
7/23 July July 7/1 8/6
8/1 July August 8/7 9/1
8/4 July September 9/2 9/29
8/7 August October 9/30 10/27
The Returned Value is what I am looking to accomplish

I uploaded a sample

Thanks

Teeroy
11-03-2012, 02:54 PM
An easy way to do this is to classify the date in the 3rd column of the first sheet like below. You can then use a Pivot Table to collate results.

Sub classify()
Dim rCurrent As Range

Set rCurrent = Sheets("FY2013").Range("B2")
Do
With Sheets("Macro")
Select Case rCurrent.Value
Case Is > .Range("C13").Value
rCurrent.Offset(0, 1).Value = "Later than specified range"
Case Is > .Range("C12").Value
rCurrent.Offset(0, 1).Value = .Range("A13").Value
Case Is > .Range("C11").Value
rCurrent.Offset(0, 1).Value = .Range("A12").Value
Case Is > .Range("C10").Value
rCurrent.Offset(0, 1).Value = .Range("A11").Value
Case Is > .Range("C9").Value
rCurrent.Offset(0, 1).Value = .Range("A10").Value
Case Is > .Range("C8").Value
rCurrent.Offset(0, 1).Value = .Range("A9").Value
Case Is > .Range("C7").Value
rCurrent.Offset(0, 1).Value = .Range("A8").Value
Case Is > .Range("C6").Value
rCurrent.Offset(0, 1).Value = .Range("A7").Value
Case Is > .Range("C5").Value
rCurrent.Offset(0, 1).Value = .Range("A6").Value
Case Is > .Range("C4").Value
rCurrent.Offset(0, 1).Value = .Range("A5").Value
Case Is > .Range("C3").Value
rCurrent.Offset(0, 1).Value = .Range("A4").Value
Case Is > .Range("C2").Value
rCurrent.Offset(0, 1).Value = .Range("A3").Value
Case Is >= .Range("B2").Value
rCurrent.Offset(0, 1).Value = .Range("A2").Value
Case Is < .Range("B2").Value
rCurrent.Offset(0, 1).Value = "Earlier than specified range"
End Select
End With
Set rCurrent = rCurrent.Offset(1, 0)
Loop While rCurrent.Value <> ""
End Sub