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/

[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 :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

[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 :rofl:

[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

:bow: