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.
(E-Mail Removed) wrote:
>
> 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
Similar Threads
