Date Range Macro

[whitethomas12]

I am having an issue on trying to use a macro to select and copy a
data from one sheet to another. I know of one way and that is to have
it search and find one entry at a time and then paste it in the next
sheet. This is very time consuming.

The following is another method that I am try but it seems to work on
only 10 rows; any more than that I will get an error message stating

Run-time error '1004'
Method 'Range' of object' _Global" Failed

Here is the code

Dim i As String
Dim j As String
Dim k 'As String
Dim l
Dim dStartDate As Date
Dim dEndDate As Date
dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/
yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/
yyyy"))
dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")
Range("A1").Select
Dim dataRange As Range

Do While ActiveCell.Value <> ""
If ActiveCell.Value >= dStartDate And ActiveCell.Value <= dEndDate +
1 Then
'If ActiveCell.Value = "dog" Then
i = ActiveCell.Row & ":" & ActiveCell.Row
End If

j = i
If j <> "" Then
k = k & "," & j
If k = "," & j Then
k = j
End If
End If
j = ""
i = ""
ActiveCell.Offset(1, 0).Select
Loop
l = "" & k & ""
Range(l).Select

The code is simple, it just records each row that meets the date range
and then puts in the the range fromat. When I use the debug it will
show Range("1:1,2;2......etc)

Will someone please help me

 

[Dave Peterson]

I'd build the range differently:

Dim dStartDate As Date
Dim dEndDate As Date
Dim dataRange As Range

dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/yyyy"))

dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")

Range("A1").Select

set datarange = nothing
Do While ActiveCell.Value <> ""
If ActiveCell.Value >= dStartDate _
And ActiveCell.Value <= dEndDate + 1 Then
if datarange is nothing then
set datarange = activecell
else
set datarange = union(datarange, activecell)
end if
end if
ActiveCell.Offset(1, 0).Select
Loop

if datarange is nothing then
msgbox "No cells found!
else
datarange.entirerow.select
end if

===
Untested, uncompiled. Watch for typos.

 

[whitethomas12]

I'd build the range differently:

Dim dStartDate As Date
Dim dEndDate As Date
Dim dataRange As Range

dStartDate = CDate(InputBox("Enter Start Date", "Valid Format - mm/dd/yyyy"))
dEndDate = CDate(InputBox("Enter End Date", "Valid Format - mm/dd/yyyy"))

dStartDate = Format(dStartDate, "mm/dd/yyyy h:mm;@")
dEndDate = Format(dEndDate, "mm/dd/yyyy h:mm;@")

Range("A1").Select

set datarange = nothing
Do While ActiveCell.Value <> ""
  If ActiveCell.Value >= dStartDate _
   And ActiveCell.Value <= dEndDate + 1 Then
     if datarange is nothing then
        set datarange = activecell
     else
        set datarange = union(datarange, activecell)
     end if
  end if
  ActiveCell.Offset(1, 0).Select
Loop

if datarange is nothing then
  msgbox "No cells found!
else
  datarange.entirerow.select
end if

===
Untested, uncompiled.  Watch for typos.















--

Dave Peterson- Hide quoted text -

- Show quoted text -

Thank you for all of your help. It worked great