Returning cell references from a date range

[funksta]

Apologies for the poor subject but I'm not too sure what it is I'm asking for.

In my spreadsheet there is a column B which contains dates. I want to be able to report on all the cells within a range based on a user entered date.

For example if the user says 20th of Feb then the range is all of the rows with today's date in column B down to all of the rows with the 20th of Feb in column B.

How do I take the dates and use them to define a range that I would like Excel to work with?

 

[Zack Barresse]

what are you trying to do with your data? just show it? are you summing values? averaging, etc??

you can specify ranges, but a little more specifics on your question is needed.

 

[funksta]

Thanks for having look.

After determining the range I'll be doing a few COUNTIF's to collate figures on types of occurences in that week. So ideally I'd like to be able to say between 20th Feb and 27th Feb covers row 15 to row 30, store that in a variable and use that variable as the range for the COUNTIF.

 

[DRJ]

Try this

Sub GetARange()

Dim MyDate          As Date
Dim c               As Object
Dim StartRow        As Long
Dim EndRow          As Long

    MyDate = InputBox("What date???")
    With Sheets("Sheet1").Range("B:B")
        Set c = .Find(What:=MyDate, LookIn:=xlFormulas, LookAt:=xlWhole, Searchdirection:=xlNext)
    End With
    If c Is Nothing Then
        MsgBox ("Sorry No Data"), vbCritical
        Exit Sub
        Else
        StartRow = c.Row
    End If
    With Sheets("Sheet1").Range("B:B")
        Set c = .Find(What:=MyDate, LookIn:=xlFormulas, LookAt:=xlWhole, Searchdirection:=xlPrevious)
    End With
    If c Is Nothing Then
        EndRow = StartRow
        Else
        EndRow = c.Row
    End If
    Range("B" & StartRow & ":B" & EndRow).Select

End Sub

 

[Zack Barresse]

a formula based approach would be making use of sumproducts multi-conditional count feature. (i hope aladin isn't looking )...

Book5
	D	E	F	G
6	2/19/2004	10
7	2/20/2004	2/20/2004
8	2/21/2004	3/9/2004
9	2/22/2004
10	2/23/2004
11	2/23/2004
12	2/24/2004
13	2/25/2004
14	2/26/2004
15	2/27/2004
16	2/28/2004
Sheet1

hope this is what your looking for.

edit: changed latter user-input to =today()
sorry, re-read post

 

[funksta]

Thanks for that DRJ, that looks like what I'm after but I've run into another problem.

Column B comes to me as custom formatted field 11/11/2003 08:41:41

I've reformatted the column using -

Columns("B:B").Select
Selection.NumberFormat = "d/m/yyyy"

Only now when it searches on dates that I can see exist in column B, it doesn't find anything.

This is probably a basic error I've made, can anyone point out where I've gone wrong please?

 

[DRJ]

Hi

the error is because you are inputting the dates in a way excel is not used to. When you input the date 7/1/04 excel thinks this in July 1, not January 7 regardless of the cell format. This will address the issue. When it asks for a date input it and it will reformat it before it searches.

Sub GetARange()

Dim MyDate          As Date
Dim c               As Object
Dim StartRow        As Long
Dim EndRow          As Long

    MyDate = InputBox("What date???")
    MyDate = Format(MyDate, "d/m/yyyy")
    With Sheets("Sheet1").Range("B:B")
        Set c = .Find(What:=MyDate, LookIn:=xlFormulas, LookAt:=xlWhole, Searchdirection:=xlNext)
    End With
    If c Is Nothing Then
        MsgBox ("Sorry No Data"), vbCritical
        Exit Sub
        Else
        StartRow = c.Row
    End If
    With Sheets("Sheet1").Range("B:B")
        Set c = .Find(What:=MyDate, LookIn:=xlFormulas, LookAt:=xlWhole, Searchdirection:=xlPrevious)
    End With
    If c Is Nothing Then
        EndRow = StartRow
        Else
        EndRow = c.Row
    End If
    Range("B" & StartRow & ":B" & EndRow).Select

End Sub