Returning cell references from a date range

funksta

New Member
Joined
Jul 7, 2002
Messages
18
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Zack Barresse

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web
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

New Member
Joined
Jul 7, 2002
Messages
18
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
Try this

Code:
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

MrExcel MVP
Joined
Dec 9, 2003
Messages
10,881
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
  3. Web

ADVERTISEMENT

a formula based approach would be making use of sumproducts multi-conditional count feature. (i hope aladin isn't looking :oops: )...
Book5
DEFG
62/19/200410
72/20/20042/20/2004
82/21/20043/9/2004
92/22/2004
102/23/2004
112/23/2004
122/24/2004
132/25/2004
142/26/2004
152/27/2004
162/28/2004
Sheet1


hope this is what your looking for.

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

funksta

New Member
Joined
Jul 7, 2002
Messages
18
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

MrExcel MVP
Joined
Feb 17, 2002
Messages
3,853
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,271
Messages
5,600,650
Members
414,399
Latest member
Ninjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top