Help with returning records given a date range

D

Daveo

Hi there,

I was wondering if anyone could give me some pointers as to where to
start with the following?

I have a spreadsheet of data in a table. One of the fields is date. I
would like the user to be able to enter a date range and have all the
records returned that fall within that range.

What function should I use for that? DGET can only return one record.
Could I use something like VLOOKUP in an array? The number of records
returned will obviously change depending on the date range entered.

Or, should I use something like a VBA loop?

I need to do this in Excel - it can't be in Access.

Any help would be much appreciated.

Many thanks - David
 
B

Biff

Hi!

If you can't filter:
I would like the user to be able to enter a date range and have all the
records returned that fall within that range.
Click to expand...

How many "records" are you talking about?

Be very specific. Explain it like this:

"I" have a data table in Sheet1 from A2:H500.

Column A are dates.

B2:H500 contains various data.

The user will enter a date range. A1 will hold the lower boundry and B1 will
hold the upper boundry.

"I" want to extract to Sheet2 all the data in columns B:H that fall within
the date range of cells A1 and B1 inclusive.

Biff
 
D

Daveo

Hi Biff,

The number of records increases at around 50 per day, but I'll clear
out ones older than 3 months old so let's say there wont be more than
5000 records. I'll probably only be looking to return about 1500 at the
very most at a time.

The data table is in Sheet1 from A2:AQ500 to start with but the 500
part will obvioulsy change as time goes on.

Lets say the lower and upper bounds are in cells A1 and B1
respectively.

I'd want to extract to Sheet2 all the data in columns A:AQ that fall
within the date range of cells A1 and B1.

What's the best way?

Many thanks - David
 
B

Biff

Hi!

Ok, that might be too much to do with formulas. It could be done but the
calculation time would be very slow.

If you can't filter then your only remaining option is VBA code. I can't
help you with that but you should try posting this in the Programming forum.

If you don't get a solution there then post back here and let me know. I'll
put together a sample file that does this with formulas.

Biff
 
B

Bruno Campanini

Daveo said:
Hi Biff,

The number of records increases at around 50 per day, but I'll clear
out ones older than 3 months old so let's say there wont be more than
5000 records. I'll probably only be looking to return about 1500 at the
very most at a time.

The data table is in Sheet1 from A2:AQ500 to start with but the 500
part will obvioulsy change as time goes on.

Lets say the lower and upper bounds are in cells A1 and B1
respectively.

I'd want to extract to Sheet2 all the data in columns A:AQ that fall
within the date range of cells A1 and B1.

What's the best way?

Many thanks - David
Click to expand...

I suppose you don't have Blanks on your Date column.
If it is not the case, please let me know.

Define StartCell, TargetCell, ColNum, LowerDate, UpperDate

-----------------------------------------------------
Sub Button36_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long

'User Definitions
'------------------------------
Set StartCell = Sheets("Sheet2").[A230]
Set TargetCell = Sheets("Sheet10").[A1]
ColNum = 4
Set LowerDate = [A228]
Set UpperDate = [A229]
'------------------------------

On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

For Each i In Range(StartCell, StartCell.End(xlDown))
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k) = i.Offset(0, k)
Next
j = j + 1
End If
Next

ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub
 
D

Daveo

Hi Bruno,

I put this code in my sheet and changed a couple of things. My start
cell is Sheet1, Cell A3. I want the data to be copied into Sheet2,
starting at Cell A5. My dates are in Sheet1, Cells A1 and B1. I have 44
columns that I need to copy. When I run the code, nothing happens at
all:


Private Sub CommandButton1_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long


'User Definitions
'------------------------------
Set StartCell = Sheets("Sheet1").[A3]
Set TargetCell = Sheets("Sheet2").[A5]
ColNum = 44
Set LowerDate = Sheets("Sheet1").[A1]
Set UpperDate = Sheets("Sheet1").[B1]
'------------------------------


On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


For Each i In Range(StartCell, StartCell.End(xlDown))
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k) = i.Offset(0, k)
Next
j = j + 1
End If
Next


ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub


Thanks - David
 
B

Bruno Campanini

Hi Bruno,

I put this code in my sheet and changed a couple of things. My start
cell is Sheet1, Cell A3. I want the data to be copied into Sheet2,
starting at Cell A5. My dates are in Sheet1, Cells A1 and B1. I have 44
columns that I need to copy. When I run the code, nothing happens at
all:
Click to expand...
[...]

Ok Daveo.
First of all, are you sure your range starting with Sheet1!A3,
cell Sheet1!A1, cell Sheet1!B1 have all the same data type?
Things may be different from what they appear to be.
Then try REMing out the following 3 lines:

On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

and tell me please what line of code Debugger points
to for error.

Bruno
 
D

Daveo

Hi Bruno,

The range has all the same data type (dd/mm/yy).

I remmed out the error handling and the debugger pointed to the
following line:

For Each i In Range(StartCell, StartCell.End(xlDown))

With the error message:

Run-time error '1004':

Method 'range' of object '_Worksheet' failed


Many thanks - David
 
B

Bruno Campanini

Daveo said:
Hi Bruno,

The range has all the same data type (dd/mm/yy).

I remmed out the error handling and the debugger pointed to the
following line:

For Each i In Range(StartCell, StartCell.End(xlDown))

With the error message:

Run-time error '1004':

Method 'range' of object '_Worksheet' failed


Many thanks - David
Click to expand...

I've reproduced your very same environment and I confirm
the procedure is working properly.
May be your Excel version is unable to read that For Each ...
line of code.
I've made some slight modification:
1 - add to Dim Section: Dim OperatingRange as Range
2 - replace the For Each line with the following ones:
Set OperatingRange = Range(StartCell, StartCell.End(xlDown))
For Each i In OperatingRange
And please tell me what Excel version you're using;
here using Excel 2003.

Bruno
 
D

Daveo

Hi Bruno,

Thanks for the reply. However I get the same error msg as before at the
following line:

Set operatingRange = Range(StartCell, StartCell.End(xlDown))

I'm using Excel 2002 SP2.

Thanks - David
 
D

Daveo

Hi Bruno.

The following works:

Private Sub CommandButton1_Click()
Dim StartCell As Range, TargetCell As Range
Dim ColNum As Byte, LowerDate As Range, UpperDate As Range
Dim i, j As Long, k As Long
Dim operatingRange As Range



'User Definitions
'------------------------------

Set StartCell = Sheets("Data").Range("A3")
Set LowerDate = Sheets("Data").Range("A1")
Set UpperDate = Sheets("Data").Range("B1")
Set TargetCell = Sheets("Sheet2").Range("A5")
ColNum = 45

'------------------------------


On Error GoTo ErrHandler
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False


Set operatingRange = Sheets("Data").Range(StartCell,
StartCell.End(xlDown))
For Each i In operatingRange
If i >= LowerDate And i <= UpperDate Then
For k = 0 To ColNum - 1
TargetCell.Offset(j, k).Value = i.Offset(0, k).Value
Next
j = j + 1
End If
Next


ErrHandler:
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

End Sub

Thanks for all your help - David
 
B

Bruno Campanini

Daveo said:
Hi Bruno.

The following works: [...]
Set StartCell = Sheets("Data").Range("A3")
Set LowerDate = Sheets("Data").Range("A1")
Set UpperDate = Sheets("Data").Range("B1")
Set TargetCell = Sheets("Sheet2").Range("A5")
ColNum = 45
Click to expand...
[...]

Well, Excel 2002 SP2 doesn't recognise [A3] as
a shortcut for Range("A3").
Useful to know!

Ciao
Bruno
 
Top