Advanced filter trouble

[notagu]

Hi all,
my VBA skills are not very strong, but I have been dabbling lately to try to improve my spreadsheets.
I have run into an issue while attempting to do an advanced filter - filter copy,

What I want to do, is specify a date range (and other parameters) and have excel return the relevant data from a back db sheet.

What I have done below is likely the MOST round-about way to accomplish this, and to top it all off, the first time I run the macro, I get an application defined or object defined error(i have noted where below), I then press debug, and run the macro again, and If (and only if) I have cell A6 highlighted, it works....this is weird, I think it is a relatively simple thing I am trying to accomplish, I've seen it done elsewhere, but I can't seem to make it work.

This is the code I have:


Sub search()
Dim L As Date

Application.ScreenUpdating = False

range("A1").Select
Rows("7:65536").Delete

For L = cells(1, 2).Value To cells(1, 3).Value Step 1
cells(4, 1).Value = L

range("results").End(xlDown).Select
ActiveCell.Offset(1, 0).Activate 'this is where I get the application defined error


range("QUERY1").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
range("Criteria"), CopyToRange:=ActiveCell, Unique:=False

Next L


'check to see if any records returned

Application.Goto Reference:="RESULTS"
ActiveCell.Offset(1, 0).range("A1").Select
If IsEmpty(ActiveCell.Value) Then
MsgBox "No records match search criteria!"
Exit Sub
End If



'this ending loop is to clear the extra row that comes in the filtercopy that shows the column titles

Dim I As Integer
For I = 7 To 10000
If cells(I, 1) = "Market Date" Then
cells(I, 1).EntireRow.Delete
End If
Next I

Application.ScreenUpdating = True

End Sub



any help that you can offer would be GREATLY appreciated, as I have been pulling out hair over this one for a day or so.

Thanks in advance,

Graeme

 

[notagu]

Hey,
I figured it out, so don't worry about it, thanks for anyone who tried though! i really appreciate it!
G