Problems implementing advanced filter code...

[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

 

[jarviscars]

Can someone please advise if this is posted in the correct area??

 

[Norman Jones]

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:

You are getting the error message because no range named 'Database' is
found.
Try defining the name in Excel before running the code.

 

[jarviscars]

Thanks for your help... worked like a treat!

(always the simplest things)

 

[GIT-R-DONE]

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.

 

[Dave Peterson]

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

 

[GIT-R-DONE]

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?

 

[Dave Peterson]

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.

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?