Solved vba error 1004 autofilter method of range class failed

February 23, 2018 at 15:37:26
Specs: Windows 7
I am running a macro to filter by the value in a combobox. Everytime i choose field number 50 I get the method out of range error but if i pick any other filed number the macro works fine. I am not sure what is so different about field 50 (column AX) that is causing this.

thanks for the help :-(

Sub DropDown10_Change()
'Branch Combobox
ActiveSheet.Unprotect
Dim mysht As Worksheet
Dim myDropDown As Shape
Dim myValBranch As String
Dim r As Range
Set r = ActiveSheet.Range("$a$10:$ax$500")

Set mysht = ThisWorkbook.Worksheets("Pipeline")
Set myDropDown = mysht.Shapes("Drop Down 10")
myValBranch = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    ActiveSheet.Range("$a$10:$ax$500").AutoFilter Field:=50, Criteria1:=myValBranch
    Range(Cells(r.Rows.Count + 1, 1), Cells(Rows.Count, Columns.Count)).EntireRow.Hidden = True
    Range("$a$10:$ax$500").Sort Key1:=Range("f10"), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
Worksheets("Pipeline").Shapes("Drop Down 10").ControlFormat.Value = 1
Range("E5").Value = "Current Filter = " & myValBranch
Range("A10").Select
End Sub


See More: vba error 1004 autofilter method of range class failed

Report •

✔ Best Answer
February 26, 2018 at 18:24:12
As far as I know, you can't use a formula as the Criteria for an AutoFilter since the cell doesn't really contain the value that you are trying to filter on. The cell displays the value, but it actually contains the formula, so AutoFilter doesn't recognize it. Since Field 50 contains an IF formula, the code can't set the filter.

So, why not use VBA to filter on the Branch Number (Field 10) instead of the Branch Name? If you let VBA determine the Branch Number based on the Drop Down choice, you can use the Branch Number as your Criteria1. First, I have a question: Why are the Branch Numbers stored as Text? If they were numbers, you wouldn't have the green warning triangle in each cell.

I converted them to numbers and then used the following code to filter on Column J. If there is a reason that they must remain as Text in the worksheet, just put quotes around the numbers in the code ("47501") so that VBA sees them as Text. In other words, to use the Branch Number as Criteria1, the values must be numbers in both the worksheet and VBA or Text strings in both the worksheet and VBA. You can't mix formats.

Bonus: If you use the method below, you can eliminate the Helper column, which you don't want anyway.

myValBranchName = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
    Select Case myValBranchName
       Case "Doral"
           myValBranchNum = 47501
       Case "Kendall"
           myValBranchNum = 47502
    End Select

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or _
    ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    ActiveSheet.Range("$a$10:$ax$500").AutoFilter Field:=10, Criteria1:=myValBranchNum

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03



#1
February 23, 2018 at 17:36:11
I assume that by "choose" and "pick" you mean that you are choosing a number from a drop down.

Obviously, without your workbook and all it's data and drop downs, we can't test anything. The only thing I noticed is that you have Field:=50 in your code.

ActiveSheet.Range("$a$10:$ax$500").AutoFilter Field:=50, Criteria1:=myValBranch

Like I said, there is no way for us to test your code, so it's kind of hard for us to figure out what is going on.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#2
February 23, 2018 at 19:00:59
Thanks for the reply; yes I have a dropdown box (drop down 10) that I choose the branch. field 50 is the criteria range (where the branch that corresponds to that row is) and MyValBranch is the criteria. In other words if column 50 matches the value of myValBranch then it shows the data. Is there a way that I can email you the sheet?

message edited by mecerrato


Report •

#3
February 23, 2018 at 19:07:33
I sent you an email address via Private Message.

Please do not share it or use it unless I ask you to. It's a throw away address, so as soon as I see "unauthorized" use, it'll be disabled. I've got tons of stuff to do this weekend, so I may not get to your workbook right away.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Related Solutions

#4
February 25, 2018 at 12:26:05
Maybe just a slight hint as to how to use the workbook and what exactly causes the error would be helpful.

Your first goal should be to make it as easy as possible for people to help you. If we have to spend half our energy figuring how a workbook that you designed is supposed to work, and how to get it to fail, that's less energy we have to put into finding a resolution to the problem.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
February 26, 2018 at 06:13:15
DerbyDad03 you are 100% right, my apologies, in my haste to get issue resolved I did not provide any information.
I have 2 combo boxes, the one to the left ("Choose MLO") works fine (although your version has no data) but the one to the right (Choose Branch) is the one that is giving me the error. It uses column 50 (AX) which is a helper column that converts the branch number in column J to the name of the branch that matches the list in the combo box (on a side note: I really do not want to use a helper column but didn't know how to make it work without one.). I am not sure why it doesn't work with column 50 because when I use a column numbered less than 50 the macro works. Unfortunately I need all columns before 50.

Report •

#6
February 26, 2018 at 18:24:12
✔ Best Answer
As far as I know, you can't use a formula as the Criteria for an AutoFilter since the cell doesn't really contain the value that you are trying to filter on. The cell displays the value, but it actually contains the formula, so AutoFilter doesn't recognize it. Since Field 50 contains an IF formula, the code can't set the filter.

So, why not use VBA to filter on the Branch Number (Field 10) instead of the Branch Name? If you let VBA determine the Branch Number based on the Drop Down choice, you can use the Branch Number as your Criteria1. First, I have a question: Why are the Branch Numbers stored as Text? If they were numbers, you wouldn't have the green warning triangle in each cell.

I converted them to numbers and then used the following code to filter on Column J. If there is a reason that they must remain as Text in the worksheet, just put quotes around the numbers in the code ("47501") so that VBA sees them as Text. In other words, to use the Branch Number as Criteria1, the values must be numbers in both the worksheet and VBA or Text strings in both the worksheet and VBA. You can't mix formats.

Bonus: If you use the method below, you can eliminate the Helper column, which you don't want anyway.

myValBranchName = myDropDown.ControlFormat.List(myDropDown.ControlFormat.Value)
    Select Case myValBranchName
       Case "Doral"
           myValBranchNum = 47501
       Case "Kendall"
           myValBranchNum = 47502
    End Select

If (ActiveSheet.AutoFilterMode And ActiveSheet.FilterMode) Or _
    ActiveSheet.FilterMode Then
  ActiveSheet.ShowAllData
End If
    ActiveSheet.Range("$a$10:$ax$500").AutoFilter Field:=10, Criteria1:=myValBranchNum

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
February 27, 2018 at 04:27:51
DerbyDad03 thank you very much, that worked. Although your method worked marvelously and allows me to eliminate the helper cell I did experiment with my original code and removed the formula from column 50 and replaced it with the branch name that I manually typed in (for testing purposes) and still got the error, I then changed the criteria1 to read column 55 and then 60 and these columns are blank yet the macro gave me the same error. I then tried columns 3, 12, and 49 (just random column numbers) and the macro did not give me the error. There is something causing the auto filter to not allow any column beyond #49. And although we have resolved the issue in this case by converting column 10 to number format and using the case statements I feel I might run into this again.

Report •

#8
February 27, 2018 at 04:53:42
After reading your response I did a little more testing. As it turns out, the problem is not with the formulas in Column AX, it seems to be related to the fact that you have some filters applied when the workbook is first opened.

If I open the workbook and then, before doing anything else, I click on the Data tab and then click the Filter button to turn off the existing filters, the macro no longer produces the error.

In my previous testing I must have performed both actions (turning off the filters and removing the formulas) before trying the Branch Name drop down.

I'l leave it to you to figure out why the "original" filters cause the problem. I'm sure you could remove them either with a Workbook_Open macro or within the code that reads the Drop Downs.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#9
February 27, 2018 at 12:17:12
Thank you, I was able to remove the filters, would have never figured that out, thanks.

Report •

#10
February 27, 2018 at 13:21:28
I didn't actually "figure it out", it was more like I tripped over it. ;-)

It would be interesting to know why, but I don't think I'll spend too much time trying to really figure it out.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

Ask Question