VBA Autofilter Loop

[hmltnangel]

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.

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

[georgiboy]

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?

[hmltnangel]

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?

[snb]

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

[georgiboy]

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

[hmltnangel]

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

[georgiboy]

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.

[hmltnangel]

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

If var(x, y) = toFind Then

is where the error occurs.

The only change I made was to the

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

[georgiboy]

Like this:

toFind = Range("C4").Value

[hmltnangel]

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.

[georgiboy]

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?

[hmltnangel]

Maybe its that Cell C4 is returned from a formula?

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

[georgiboy]

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.

[hmltnangel]

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.

            If var(x, y) = toFind Then

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

[georgiboy]

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:

    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

[hmltnangel]

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

[hmltnangel]

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

[georgiboy]

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?

[hmltnangel]

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

[hmltnangel]

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/