Using vlookup in VBA based on autofilter criteria

Nice spotting (duh! - how stupid of me).

However I get a different error now -
Q3iuSo5.png

At which line do you get that error.

That error often happens, when you are trying to assign incompatible data type to variable.

It would be easier to help, if you can upload sample workbook with enough data and manually inputted expected result.

To upload workbook, find "Go Advanced" button in quick reply menu and click. Find "Manage Attachment" hyperlink.
Click on it and it will launch another tab/window for managing file upload.

The first If statement is where the error is occuring.

The paperclip option isn't giving me anything in the dropdown menu, cannot close the "managed attachments" tab either. And I guess I haven't posted enough to attach a link to it.

The images in the Original Post cover all the used cells though.

Hmm, I suspect below part to be the culprit.

Please Login or Register  to view this content.

What are you trying to do in this portion? Rows should have number as it's argument, not string.

As well, rows will return range object, and you cannot subtract range object.

Please Login or Register  to view this content.

This is meant to create an array such as:
{0, 1, 2, 3, ..., n}

Which when used with the Offset function with A2 as reference, return {"[A2.Value]", "[A3.Value]", "[A4.Value]",...}.
Then the subtotal with code "3" is meant to apply a 1 for visible and 0 for non-visible cells.
Therefore you get the classic -

Please Login or Register  to view this content.

Also tried:

Please Login or Register  to view this content.

But at this point I'm clutching at straws

Hmm, from what I understand, you are just checking if "A" to "F" if it's the only visible cell for the range?

Likely issue is the difference in how range is treated in Worksheet function and VBA.

You could try using Evaluate instead.
Ex:

Please Login or Register  to view this content.

Though there are more efficient methods than above.

Such as using .SpecialCells(xlCellTypeVisible) to check visible cells in range. Though I'm still unclear on what exactly you are after as end result.

Before I try out your method, I'll try to clarify what I'm trying to achieve.

I need to know the filter criteria of the first field in the autofilter in the "Outputs" Sheet. Based on the filter criteria, I wish to use vlookup to find a certain value attached to it. If there was an easy way to output the filter criteria, that would be quickest but it seems a comparison such as between the count of all visible cells in the filter range and the count of all visible cells (obeying the filtered criteria) would give you an indication of the filter criteria used.

An example would be:
You are arranging boxes on the ground and each box type has a different footprint (area in sq. something). So the program will allow you to filter box types and some other property like colour (indicated by criteria field 2) and then based on the choice of filter, you can find out the area that box type occupies.

But as long as it is possible to macro-tise the really long IFS code as follows, then we're home (as it works):

Please Login or Register  to view this content.

By the way, your code works like a charm. Thanks for all the help.

Any ideas on how to add more levels of efficiency to it?

FYI, you can use something like below to get filter criteria.

Please Login or Register  to view this content.