VBA Autofilter Loop

[georgiboy]

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:

    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.

[hmltnangel]

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

[hmltnangel]

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

And it works fine now I corrected that one cell.

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

[hmltnangel]

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.

[georgiboy]

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

[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

[georgiboy]

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

[hmltnangel]

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