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