Help with returning records given a date range

"Daveo" <writetodaveo@gmail.com> wrote in message
news:1127273931.540930.127830@g14g2000cwa.googlegroups.com...
> 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

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

Bruno

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

"Daveo" <writetodaveo@gmail.com> wrote in message
news:1127347425.506307.321300@z14g2000cwz.googlegroups.com...

> 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:
[...]

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

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

"Daveo" <writetodaveo@gmail.com> wrote in message
news:1127433379.784581.29540@g49g2000cwa.googlegroups.com...
> 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

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

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

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