Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
I've inquired about this before, but it still isn't working. I'm trying
to get XL to determine which cells are to be filtered by using the CurrentRegion method (or whatever it is). The filter range varies from datasheet to datasheet. I've declared FilterRange as a range, but can't get XL to set it equal to the current region from cell A1. Any ideas how to do this? RecSht.Select Range("a1").Select Set FilterRange = Selection.CurrentRegion.Select<-----Object required error CtyExtr.Select RecSht.Range("FilterRange").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False Thanks again! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
RecSht.Select
Set FilterRange = Range("a1").CurrentRegion CtyExtr.Select RecSht.Range("FilterRange").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False -- HTH Bob Phillips "davegb" wrote in message oups.com... I've inquired about this before, but it still isn't working. I'm trying to get XL to determine which cells are to be filtered by using the CurrentRegion method (or whatever it is). The filter range varies from datasheet to datasheet. I've declared FilterRange as a range, but can't get XL to set it equal to the current region from cell A1. Any ideas how to do this? RecSht.Select Range("a1").Select Set FilterRange = Selection.CurrentRegion.Select<-----Object required error CtyExtr.Select RecSht.Range("FilterRange").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False Thanks again! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Thanks for your reply, Bob.
I changed the macro, and am not getting an "application or object defined error" when I apply the filter. Any suggestions? By the way, I had a watch on FilterRange and there still is no value. Does that tell us anything? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Should have read "now getting" instead of "not getting" davegb wrote: Thanks for your reply, Bob. I changed the macro, and am not getting an "application or object defined error" when I apply the filter. Any suggestions? By the way, I had a watch on FilterRange and there still is no value. Does that tell us anything? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Difficult to test as I don't see the data, but try this
Set FilterRange = RecSht.Range("A1").CurrentRegion CtyExtr.Select FilterRange.AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("AA1:AA2"), _ CopyToRange:=Range("A5"), Unique:=False -- HTH Bob Phillips "davegb" wrote in message oups.com... Should have read "now getting" instead of "not getting" davegb wrote: Thanks for your reply, Bob. I changed the macro, and am not getting an "application or object defined error" when I apply the filter. Any suggestions? By the way, I had a watch on FilterRange and there still is no value. Does that tell us anything? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Thanks, Bob.
It's actually 2 macros. The first gets data selected by the user and identifies which sheet to get the records from (RecSht). Then it calls the other macro which gets the appropriate records from RecSht and pastes them to yet another sheet (CntyRec). I've omitted the rest of the second macro for simplicity. Public CurRow As Integer Public RecSht As Object 'Records sheet Public FilterRange As Range Sub InCareExtr() 'Password used Set CtyExtr = ActiveWorkbook.Sheets("County Extract") 'Application.ScreenUpdating = False HomeSht = ActiveSheet.Name CurRow = ActiveCell.Row CtyCode = ActiveSheet.Cells(CurRow, "B") If Len(Trim(ActiveSheet.Cells(CurRow, "E").Text)) 0 Then Set RecSht = ActiveWorkbook.Sheets("In Care Records") AllExtract Else MsgBox "There are no In Care for " & CtyCode & " for SFY 2005 2nd Quarter", vbOKOnly End If End Sub Const PWORD As String = "dave" Sub AllExtract() CtyExtr.Select With RecSht .Unprotect Password:=PWORD .Range("aa2") = CtyCode End With CtyExtr.Select CtyExtr.UsedRange.Clear Range("a1:e1").Merge Range("a1").FormulaR1C1 = _ "WARNING: This data will be erased the next time County Records are extracted. " With Range("a1").Characters(Start:=1, Length:=78).Font .FontStyle = "Bold" .ColorIndex = 3 End With Rows("1:1").RowHeight = 25 Range("a1").WrapText = True Range("A2:e2").Merge Range("A2").FormulaR1C1 = _ "If you wish to save the data, copy and paste it to another spreadsheet or print it before doing another data extraction." With Range("A2").Characters(Start:=1, Length:=124).Font .ColorIndex = 3 End With Range("a2").Select Selection.WrapText = True 'RecSht.Select 'Set FilterRange = Range("a1").CurrentRegion CtyExtr.Select RecSht.Range("A1:U4000").AdvancedFilter Action:= _ xlFilterCopy, CriteriaRange:=RecSht.Range("aa1:aa2"), _ CopyToRange:=Range("A5"), Unique:=False ' Set FilterRange = RecSht.Range("A1").CurrentRegion ' CtyExtr.Select ' FilterRange.AdvancedFilter Action:= _ ' xlFilterCopy, CriteriaRange:=RecSht.Range("A*A1:AA2"), _ ' CopyToRange:=Range("A5"), Unique:=False The remarked out code is your last suggestion, which still gives an Object not defined error. The other code works, using the range A1:U4000, that being the largest filter range of any of the datasheets involved at this time. But the data will be updated quarterly, and I don't want to have to remember to edit this area in case next quarter's data is larger than the current region. There must be a way to tell XL just to filter the current region, but this has been driving me nuts for a couple of weeks! I tried using a range name, but that just seemed to complicate matters significantly more. Any ideas? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
I found the problem! There was one of those irritating little dashes
that are inserted in the code when it's copied from Google into a module. It's working now. Thanks for your help, Bob! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Ron de Bruin forced me to send a message to Google.
http://groups-beta.google.com/support/bin/request.py Maybe if enough people complain, ... davegb wrote: I found the problem! There was one of those irritating little dashes that are inserted in the code when it's copied from Google into a module. It's working now. Thanks for your help, Bob! -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
Just sent my complaint!
Dave Peterson wrote: Ron de Bruin forced me to send a message to Google. http://groups-beta.google.com/support/bin/request.py Maybe if enough people complain, ... davegb wrote: I found the problem! There was one of those irritating little dashes that are inserted in the code when it's copied from Google into a module. It's working now. Thanks for your help, Bob! -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
What object is it looking for?
You're gonna get a reply back from Google, too.
davegb wrote: Just sent my complaint! Dave Peterson wrote: Ron de Bruin forced me to send a message to Google. http://groups-beta.google.com/support/bin/request.py Maybe if enough people complain, ... davegb wrote: I found the problem! There was one of those irritating little dashes that are inserted in the code when it's copied from Google into a module. It's working now. Thanks for your help, Bob! -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2 Label Options - Forms Object vs Control Box Object | Excel Discussion (Misc queries) | |||
Confusion about how the Window object fits into the Excel object model | Excel Programming | |||
returning pivottable object from a range object | Excel Programming | |||
How to Add an Object to the New Object List in windows 2000 | Excel Programming | |||
Range object to Array object conversion | Excel Programming |