Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
I've the following problem. When the the below code runs i get
"Autofilter method of range class failed". It says the problem is wit the last line of code. The strange thing is that it even filters th selected range, but it still has a problem with it. If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber This thing is driving me nuts. Any help you guys can give would b appreciated. Thanks Jame -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
Hi,
did you try Sheets("PG&E").Range("ListPGE").AutoFilter 1, frmECR.cboAccountNumbe -- Message posted from http://www.ExcelForum.com |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
hmmm...
I would define a Range object and set it manually. Like this : Dim r as Range ... set r = Sheets("PG&E").Range("A1:B5") 'the address of the table r.AutoFilter 1, frmECR.cboAccountNumber --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
it still doesn't work. Talk about frustrating huh. This thing is killin
me -- Message posted from http://www.ExcelForum.com |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
Try
If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber.Text -- Regards, Tom Ogilvy Jmbostock wrote in message ... I've the following problem. When the the below code runs i get a "Autofilter method of range class failed". It says the problem is with the last line of code. The strange thing is that it even filters the selected range, but it still has a problem with it. If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber This thing is driving me nuts. Any help you guys can give would be appreciated. Thanks James --- Message posted from http://www.ExcelForum.com/ |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
This worked fine for me:
Private Sub CommandButton1_Click() If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber.Text End If End Sub Private Sub UserForm_Initialize() cboVendor.AddItem "ABC" cboVendor.AddItem "EF&G" cboVendor.AddItem "PG&E" cboVendor.AddItem "MN&O" cboAccountNumber.AddItem 1 cboAccountNumber.AddItem 2 cboAccountNumber.AddItem 3 End Sub The range ListPGE was located on sheet PG&E -- Regards, Tom Ogilvy Tom Ogilvy wrote in message ... Try If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber.Text -- Regards, Tom Ogilvy Jmbostock wrote in message ... I've the following problem. When the the below code runs i get a "Autofilter method of range class failed". It says the problem is with the last line of code. The strange thing is that it even filters the selected range, but it still has a problem with it. If frmECR.cboVendor.Text = "PG&E" Then Sheets("PG&E").Range("ListPGE").AutoFilter _ Field:=1, _ Criteria1:=frmECR.cboAccountNumber This thing is driving me nuts. Any help you guys can give would be appreciated. Thanks James --- Message posted from http://www.ExcelForum.com/ |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
Still doesn't work. I cut the code down to the bare minimun just in case
something was effecting it that i didn't count on. The userform initializes Then I click on the cbovendor and select one of three items from a list, one of them being "PG&E". Private Sub cboVendor_Change() If frmECR.cboVendor.Text = "PG&E" Then frmECR.cboAccountNumber.RowSource = "PGEAccount" End If End Sub I then click on the cboAccountnumber, where it gives me a list of account numbers. I select an account number and the following code runs. Private Sub cboAccountNumber_Change() Sheets("PG&E").Select Range("ListPGE").Select Selection.AutoFilter Field:=1, Criteria1:=frmECR.cboAccountNumber.Text End Sub This isn't the code it was previously, i instead recorded a macro and copied it it, but it still didn't work. any ideas??? --- Message posted from http://www.ExcelForum.com/ |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
You need a line continuation character in the filter command:
Private Sub cboAccountNumber_Change() Sheets("PG&E").Select Range("ListPGE").Select Selection.AutoFilter Field:=1, _ Criteria1:=frmECR.cboAccountNumber.Text End Sub either that or put it all on one line. -- Regards, Tom Ogilvy Jmbostock wrote in message ... Still doesn't work. I cut the code down to the bare minimun just in case something was effecting it that i didn't count on. The userform initializes Then I click on the cbovendor and select one of three items from a list, one of them being "PG&E". Private Sub cboVendor_Change() If frmECR.cboVendor.Text = "PG&E" Then frmECR.cboAccountNumber.RowSource = "PGEAccount" End If End Sub I then click on the cboAccountnumber, where it gives me a list of account numbers. I select an account number and the following code runs. Private Sub cboAccountNumber_Change() Sheets("PG&E").Select Range("ListPGE").Select Selection.AutoFilter Field:=1, Criteria1:=frmECR.cboAccountNumber.Text End Sub This isn't the code it was previously, i instead recorded a macro and copied it it, but it still didn't work. any ideas??? --- Message posted from http://www.ExcelForum.com/ |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
Even when i use the line continuation, or all on one line, it stil
gives me that Autofilter method range class error. This thing is unbelievable. i've no idea what's wrong with it -- Message posted from http://www.ExcelForum.com |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
is the sheet protected?
If you enter ListPGE in the name box and hit enter (ListPGE should be selected) can you then do Data=filter=Autofilter and select an account number in the first dropdown. It has to be something particular to your environment. Applying an autofilter really isn't that hard (any merged cells or anything else unusual)? -- Regards, Tom Ogilvy Jmbostock wrote in message ... Even when i use the line continuation, or all on one line, it still gives me that Autofilter method range class error. This thing is unbelievable. i've no idea what's wrong with it. --- Message posted from http://www.ExcelForum.com/ |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Autofilter nightmare
Thanks for everyones help, but i'm calling it quits on this particular
project. I think the problem is related to something in the sheets, rather than something i've written. I'm going to start from scratch and hope for the best. Again, you guys are awesome. James --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Nightmare | Excel Worksheet Functions | |||
PivotTable Nightmare | Excel Discussion (Misc queries) | |||
PivotTable Nightmare | Excel Discussion (Misc queries) | |||
IF Statement nightmare | Excel Discussion (Misc queries) | |||
Protection nightmare | Excel Programming |