Error 1004 Select method of range class failed

J

jams

New member
Joined
Jun 14, 2013
Messages
9
Reaction score
0
Points
0
Hi, I have this code that I am trying to generate a report
Code: 
rivate Sub Cmdprintpreview_Click()
 
    Dim GetDate
    Dim strMonth, strYear
    Dim ws As Worksheet
 
   Set ws = Sheets("Returns")
   
    'get values from comboboxes
    strMonth = Me.Cbomonth.Value
    strYear = Me.Cboyear.Value
    
    
    GetDate = strMonth & "/" & strYear
    
    
    ws.Range("Period").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=3, Criteria1:=GetDate
    
    Unload Me
    ActiveSheet.PrintPreview    
    Selection.AutoFilter   
    FrmReport.Show
I am getting this error can anyone help thanks
 
IF you push DEBUG when the error comes up, what line is highlighted?

If it's the ews.Range("Period").Select line, then you need to activate the sheet first.

with ews
.activate
.range("Period").Select
end with
 
It is highlighting this line ws .Range("Period").Select
 
It didn't I'm getting a compilation error
 
How about (air code)
Code: 
Private Sub Cmdprintpreview_Click()
     Dim GetDate
    Dim strMonth, strYear
    Dim ws As Worksheet
    Set ws = Sheets("Returns")
       'get values from comboboxes
    strMonth = Me.Cbomonth.Value
    strYear = Me.Cboyear.Value
            GetDate = strMonth & "/" & strYear
with ws.Range("Period")
.AutoFilter
.AutoFilter Field:=3, Criteria1:=GetDate
        Unload Me
ws.PrintPreview
.AutoFilter
end with
FrmReport.Show
 
My mistake, that should have been:
Code: 
with ws
     .activate
     .range("Period").Select
end with

I accidentally typed an e in front of ws.

And JoePublic makes an excellent point that you don't always have to select something to do something to it. Although you do have to select a worksheet if you then want to select a range within that sheet.
 
I realized the mistake I am still getting the runtime error on this line Selection.AutoFilter

thanks
 
Thanks Joe

I tried the code but is receiving the runtime error on this line .AutoFilter
 
Last edited:
jams said:
it is referring to one column.
Click to expand...

That's the problem, I suspect. The Autofilter is trying to filter column 3 of this range as per the below:
Code: 
ws.Range("Period").Select     Selection.AutoFilter     Selection.AutoFilter Field:=3, Criteria1:=GetDate

...but there IS no column 3, because "Period" is just one column.

To fix, you probably either need to replace "Period" with a larger range (and adjust the Field argument so that it references the Period column) or you need to change Field:=3 to Field:=1
 
Thanks I replaced "Period" with "C1" and it works
 
Cool. Note that it's much safer to refer to named ranges than cell addresses, because named ranges are resistant to row or column deletions/inserts. Whereas if you use cell addresses then if someone adds or deletes rows or columns to the left or above of your cell address, then the code will no longer reference the correct cell. (VBA will still be pointing to the hard-coded "C1", whereas if you were to say add another column then your actual data would be in D1.)
 
thanks i agree that is why I used the named ranged instead of the cell. Can you have a look at the code and tweek it using the named range

thanks
 
Hi Jams. As per my above comment, you need to change Field:=3 to Field:=1

That should do it.
 
You must log in or register to reply here.
Back
Top