Problems implementing advanced filter code...

  • Thread starter Thread starter jarviscars
  • Start date Start date
J

jarviscars

Hi all,

I seem to be having trouble implementing some code from Debra
Dalgleish... I posted my original question in the 'functions' section
as I was looking for a formula however I think this area may be more
suitable for my question now that i'm trying to use Debra's sample...

The original thread is at
http://www.excelforum.com/showthread.php?t=390438

The Advanced Filter sample seems to do exactly what i want it to do but
when I change the code to suit my workbook, I get a runtime error...


Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug>> the vb editor seta a break point at line 10


Code:
--------------------
Set rng = Range("Database")
--------------------


Am I missing something completely obvious???
(Code below)

Thanks in advance.



Code:
--------------------
Option Explicit

Sub ExtractLocations()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Cars without Photos")
Set rng = Range("Database")

'extract a list of Locations
ws1.Columns("B:B").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("B1").Value

For Each c In Range("J2:J" & r)
'add the Location to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A2"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Cars without Photos").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A2"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function
 
Hi Javiscars,
Run-time error '1004':
Method 'Range' of object '_Global' failed

When I click <<Debug>> the vb editor seta a break point at line 10


Code:
Click to expand...

You are getting the error message because no range named 'Database' is
found.
Try defining the name in Excel before running the code.
 
Using the same macro that JarvisCars is using, I am trying to get data
extracted from one sheet and pasted into multiple sheets with the sheet
names becoming the date of the data extracted in the ddd-d format (ie
Thur-29 or Fri-30).

My macro looks like this (similar to JarvisCars):

Code:
--------------------
Option Explicit

Sub ExtractReps()
Dim ws1 As Worksheet
Dim wsNew As Worksheet
Dim rng As Range
Dim r As Integer
Dim c As Range
Set ws1 = Sheets("Sheet1")
Set rng = Range("Database")

'extract a list of Sales Reps
ws1.Columns("A:A").Copy _
Destination:=Range("L1")
ws1.Columns("L:L").AdvancedFilter _
Action:=xlFilterCopy, _
CopyToRange:=Range("J1"), Unique:=True
r = Cells(Rows.Count, "J").End(xlUp).Row

'set up Criteria Area
Range("L1").Value = Range("A1").Value

For Each c In Range("J2:J" & r)
'add the rep name to the criteria area
ws1.Range("L2").Value = c.Value
'add new sheet (if required)
'and run advanced filter
If WksExists(c.Value) Then
Sheets(c.Value).Cells.Clear
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=Sheets(c.Value).Range("A1"), _
Unique:=False
Else
Set wsNew = Sheets.Add
wsNew.Move After:=Worksheets(Worksheets.Count)
wsNew.Name = c.Value
rng.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=Sheets("Sheet1").Range("L1:L2"), _
CopyToRange:=wsNew.Range("A1"), _
Unique:=False
End If
Next
ws1.Select
ws1.Columns("J:L").Delete
End Sub
Function WksExists(wksName As String) As Boolean
On Error Resume Next
WksExists = CBool(Len(Worksheets(wksName).Name) > 0)
End Function

--------------------


Mine dies and debug highlights the following row:

Code:
--------------------
wsNew.Name = c.Value
--------------------


Now, I know that this is because it is referencing the date in it's
number format, not as just a text value. Is there a way to get it to
just look at the text results, and not the formula when naming the
sheets?

I appreciate any and all help on this. I am fairly new to VBA and
learning on the fly.
 
How about:

wsNew.Name = c.Value
becoming
wsNew.Name = format(c.Value, "ddd-d")

If C.value is a date like 12/29/2005, then that's an invalid sheet name (no
slashes allowed).
 
Dave, that worked great for 3 of my 4 reports that I am automating. Now
on the one that I am getting stumped on, you might be able to help me
out with as well. My field that I am filtering by is a date and time
field (ie "12/1/2005 12:52:39 AM") I am only wanting to filter by the
date part of it. I am tried using a loop that brought out the
month/day/year out of that, but the filter tries to filter by the
fomula and not the results of the formula.

My loop is this:

Code:
--------------------
Range("A2").Select
Do
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[2]),MONTH(RC[2]),DAY(RC[2]))"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 2))

--------------------


So the contents of the cells would be
"=DATE(YEAR(C3),MONTH(C3),DAY(C3))" and when the filter macro hits
that, the contents of the L look like this
"DATE(YEAR(J3),MONTH(J3),DAY(J3))" when I need them to be 12/03/2005.


Does that make sense? Is there anything that I can do to the Filter
Macro to make it pull the date only out of the date and time field, or
should I keep my loop in there, that extracts the date. If so, can the
filter macro be changed to pull the results of the loop instead of the
formula?
 
I think you should be able to use a formula like:

activecell.formular1c1 = "=int(rc[2])"

format it as a date.

And if you're getting the string in the cell that includes the equal sign, then
you could do:

with activecell
.numberformat = "General"
.formular1c1 = "=whateverformulayoulike"
end with

If you're really getting the "Date(...)" displayed, I'd guess that you didn't
include the leading equal sign in your code.

GIT-R-DONE said:
Dave, that worked great for 3 of my 4 reports that I am automating. Now
on the one that I am getting stumped on, you might be able to help me
out with as well. My field that I am filtering by is a date and time
field (ie "12/1/2005 12:52:39 AM") I am only wanting to filter by the
date part of it. I am tried using a loop that brought out the
month/day/year out of that, but the filter tries to filter by the
fomula and not the results of the formula.

My loop is this:

Code:
--------------------
Range("A2").Select
Do
ActiveCell.FormulaR1C1 = "=DATE(YEAR(RC[2]),MONTH(RC[2]),DAY(RC[2]))"
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell.Offset(0, 2))

--------------------

So the contents of the cells would be
"=DATE(YEAR(C3),MONTH(C3),DAY(C3))" and when the filter macro hits
that, the contents of the L look like this
"DATE(YEAR(J3),MONTH(J3),DAY(J3))" when I need them to be 12/03/2005.

Does that make sense? Is there anything that I can do to the Filter
Macro to make it pull the date only out of the date and time field, or
should I keep my loop in there, that extracts the date. If so, can the
filter macro be changed to pull the results of the loop instead of the
formula?
Click to expand...
 
Back
Top