Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
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 ------------------------------------------------------------------------ jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631 View this thread: http://www.excelforum.com/showthread...hreadid=390833 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
Can someone please advise if this is posted in the correct area?? -- jarviscar ----------------------------------------------------------------------- jarviscars's Profile: http://www.excelforum.com/member.php...fo&userid=2563 View this thread: http://www.excelforum.com/showthread.php?threadid=39083 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
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: -------------------- Set rng = Range("Database") -------------------- You are getting the error message because no range named 'Database' is found. Try defining the name in Excel before running the code. --- Regards, Norman "jarviscars" wrote in message ... 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 ------------------------------------------------------------------------ jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631 View this thread: http://www.excelforum.com/showthread...hreadid=390833 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
Thanks for your help... worked like a treat! (always the simplest things) -- jarviscars ------------------------------------------------------------------------ jarviscars's Profile: http://www.excelforum.com/member.php...o&userid=25631 View this thread: http://www.excelforum.com/showthread...hreadid=390833 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter 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. -- GIT-R-DONE ------------------------------------------------------------------------ GIT-R-DONE's Profile: http://www.excelforum.com/member.php...o&userid=29960 View this thread: http://www.excelforum.com/showthread...hreadid=390833 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
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 wrote: 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. -- GIT-R-DONE ------------------------------------------------------------------------ GIT-R-DONE's Profile: http://www.excelforum.com/member.php...o&userid=29960 View this thread: http://www.excelforum.com/showthread...hreadid=390833 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter 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? -- GIT-R-DONE ------------------------------------------------------------------------ GIT-R-DONE's Profile: http://www.excelforum.com/member.php...o&userid=29960 View this thread: http://www.excelforum.com/showthread...hreadid=390833 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Problems implementing advanced filter code...
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 wrote: 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? -- GIT-R-DONE ------------------------------------------------------------------------ GIT-R-DONE's Profile: http://www.excelforum.com/member.php...o&userid=29960 View this thread: http://www.excelforum.com/showthread...hreadid=390833 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to replace Advanced Filter | Excel Discussion (Misc queries) | |||
Why won't advanced filter return filter results? | Excel Worksheet Functions | |||
"Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Advanced Filter Problems | Excel Discussion (Misc queries) | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |