VBA Autofilter Loop

note, asked a variant of this question on mrexcel as well, i'll update if a solution in the other ay becomesavailable.

So, I have a table called "Talent".

I wish to autofilter the table based on 8 columns. The value in "$C$4" will appear in only one of the columns, although it may be a different one every time. So i need it to try each column until it finds results and then exit the loop leaving just those results on screen. I got as far as this, with my limited VBA knowledge, but keep getting an error. (Runtime 91)

I may be barking up the wrong tree, but hopefully im on the right tracks. Any suggestions for correcting would be appreciated.

Code:

---

Sub Autofilterallcolumns2()
Application.ScreenUpdating = False
With Sheets("Talent")
      Rows("13:1250").Hidden = False
End With
Application.ScreenUpdating = True
Dim RTF As Range, i As Long
Set RTF = Range("Talent")
With Sheets("Talent")
    For i = 11 To 18
        .Activate
        .AutoFilterMode = False
            RTF.AutoFilter
            RTF.AutoFilter field:=10, Criteria1:=Cells(4, 3).Value
            If .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count >= 2 Then
            Exit For
            ElseIf .AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Count < 2 Then
            Call Clear_All2
            End If
  Next
End With
End Sub

---

Just a thought:

Would it be not be easier to search for the value in the whole table, when found return the column number, use this column number to identify the column to autofilter?

Possibly. The value may exist in other columns aside from those eight though. It has to be filtered on whether the Value appears in those eight not the others.

Admittedly my VBA is weak, but how would I do that?

Code:

---

Sub M_snb()
  msgbox columns(1).resize(,8).find( "Value").column
End Sub

---

Do you have a sheet called 'Talent' and a range named 'Talent' on the 'Talent' sheet?

Yes, the sheet is called talent, and the table is called talent (on the talent sheet)

As we can't be sure the table starts at column 1 i have decided to put the table data into an array and search for the value starting at the column of choice.

If the table starts in column 2 then using .Find and .Column will return the worksheet column number and not the column index of the table.

I could not find a way of returning the table column index from a range, a range that we have found using .Find for example.

Using the attached method would mean that it does not matter where the table is on the worksheet.

Thanks Georgiboy

The table does indeed start in column 2 (B) of the worksheet. This works fine on the test sheet you ttached, however when I transfer it to the Workbook I use, it has an error.

Run Time Error 13

Type Mismatch

Debug then shows at

Code:

---

If var(x, y) = toFind Then

---

is where the error occurs.

The only change I made was to the

Code:

---

To find = "Dave"

---

part of the code, where I changed "Dave" to the cell ref "$C$4" which I suspect is the issue, is there a way to reference a cell here for the string instead of the actual text

Like this:

Code:

---

toFind = Range("C4").Value

---

Oddly this works on the test sheet, but on the Talent Sheet it hits the Run Time 13 error - Type Mismatch. At the same point as before.

Has the value in C4 got trailing spaces on the end?
Maybe the data in the table has trailing spaces?

I would guess that the value in C4 does not match the values in the table for some reason.

You could test the cells value length with: MsgBox len(range("C4"))

Are you able to upload a sample workbook?

Maybe its that Cell C4 is returned from a formula?

Both the data in the table, and Cell C4 are formula driven

If I edit the example to be all formula it still seems to work, attached.

Maybe the visual value of C4 is not the actual value, copy and paste C4 as values somewhere else and look at the value in the formula bar. See if there are any spaces.

I have added a trim function to the attached to trim any spaces that may be on the end of the search string.

Would be easier with a sample file, even trimmed down with sensetive data removed. Just enough to display the error.

I cant remove enough out of the sheet to get it uploadable sadly.

I transferred the columns and details into your sheet and it works fine. So I suspect its looking in a column that may have an #N/A error somewhere in it.

If I open the VBA, and highlight sections - it shows "Error 2042" on var(x, y) part of the code.

Code:

---

            If var(x, y) = toFind Then

---

When highlighted I see - "Var(x, y)=error2042"

You have two options then at this stage, handle the errors in the table with =IfError(Formula,"")

Or we add on error resume next to the code to overlook the errors as below:

Code:

---

    On Error Resume Next   
    For x = 1 To UBound(var) ' rows
        For y = 11 To UBound(var, 2) ' columns
            If var(x, y) = toFind Then
                tblCol = y: GoTo foundit
            End If
        Next y
    Next
    On Error GoTo 0

---

Once I went through and removed any #N/A errors elsewhere in the sheet, it changed to error 2015

Most of the table has the iferror formulas in it - there were two that didnt and theyve now been fixed.

This now returns error 2015 instead. If I use the Error handler as suggested, it filters the table to nothing at all

the 2015 could be caused by another error in the table: '#VALUE'

Strange how it filters to nothing, try replacing the Range("C4").Value for the actual value. Just in case the formula that gives you the search string is creating a strange string.

What line of code is highlighted when you get the error?

Same line Georgiboy.

If I hover over the var(x, y) - then I see Var(x,y) = Error 2015, I hover over the ToFind, then I see = the correct value to search for

Ok, then - I found this piece of info around this error:

Apparently this particular error occurs when the parsed function returns a string value having a length above 255 characters. As long as the length of the string is less than or equal to 255 characters, the return value is the desired string. Above 255 characters, the function “crashes”.

Now - quite a few cells in the table have string values in excess of 255 So is there a way to skip those?

https://dutchgemini.wordpress.com/2009/08/07/error-2015-using-application-evaluate-in-excel-vba/

How many columns are in the table you are working on, the provided code starts looking at column 11 onwards within the table. If your table is smaller for exampe you would need to edit the bit in red below:

Code:

---

    On Error Resume Next       
    For x = 1 To UBound(var) ' rows
        For y = 11 To UBound(var, 2) ' columns
            If var(x, y) = toFind Then
                tblCol = y: GoTo foundit
            End If
        Next y
    Next
    On Error GoTo 0

---

Other than that it's tricky without a sample file.

The 11 above is the first column to start searching for the value in the table.

If the problem is being caused by the 255+ characters, then could we only look at columns 11-19 and this would avoid the error?

The entire table is around 50 columns, but its only the columns 11-19 we are looking in.

OK, If I copy and paste the entire table (as text) into your test sheet, it works fine. So something somewhere in mines must be causing an issue. I will move your sheet into mines, then tidily move the data over to hat one. Hopefully that should solve it

Damn! There was a random #Value error I hadnt noticed :doh:

And it works fine now I corrected that one cell. :banghead::crying:

Thanks Georgiboy, I shouldve spotted that way sooner.

Your code works perfect once I ensure that there are no #Value, or #N/A errors in the table

Code:

---

Option Explicit


Sub test()
    Dim wsRTF As Worksheet
    Dim tblRTF As ListObject
    Dim var As Variant
    Dim toFind As String
    Dim x As Long, y As Long
    Dim tblCol As Integer
   
    toFind = Range("A1").Value
    Set wsRTF = Sheets("Talent")
    Set tblRTF = wsRTF.ListObjects("Talent")
    var = tblRTF.DataBodyRange
   
    For x = 1 To UBound(var) ' rows
        For y = 11 To UBound(var, 2) ' columns
            If var(x, y) = toFind Then
                tblCol = y: GoTo foundit
            End If
        Next y
    Next
foundit:
    tblRTF.DataBodyRange.AutoFilter tblCol, toFind
End Sub

---

Remove any errors from the table or code will have an error.

The attached is working with long strings and formulae that result in long strings.

It works fine now. Thank you so much for helping. I would never have gotten there on my own. The single cell with a #Value error was causing the issue.

thanks for bearing with me

As a little tip - if you hover over the "x" or "y" on that line it shows you the row/column where the error exists

Quote:

---

Originally Posted by hmltnangel

It works fine now. Thank you so much for helping. I would never have gotten there on my own. The single cell with a #Value error was causing the issue.

thanks for bearing with me

As a little tip - if you hover over the "x" or "y" on that line it shows you the row/column where the error exists

---

Happy to help

As for the tip - it would have helped if I could see your data :rofl:

Dont I know it - I wish I could have. Our internal systems would have had a field day if I had uploaded the sheet itself. anonymised or not. But all worked out fantastic in the end. Now im just tidying it all back to the way I had it - errors corrected

:bow: