Advanced filter trouble

notagu

New Member
Joined
Mar 26, 2009
Messages
2
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

notagu

New Member
Joined
Mar 26, 2009
Messages
2
Hey,
I figured it out, so don't worry about it, thanks for anyone who tried though! i really appreciate it!
G
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,962
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top