Currently using Indirect - want to replace with VBA

Please Login or Register  to view this content.

This returns the correct range for my index. It basically sets the index range to look in the worksheet from the first instance of a value (that is in A142) to the last instance. So If A142 is on the worksheet 4 times, and the first time it shows up is row 100 - the range is set for AccesoriesCust!H100:I104

BUT that gives thousands of Indirect() - need to have VBA .function

Please Login or Register  to view this content.

etc...
If I could get the find to work that would give the start of the range, and add a count of the number of A142's in the range would give the end of the range.

Any help would be appreciated.

If you plan to go into custom vba function, it may be a good idea to prepare a final function, not a part to be used with index.

By the way, it would be probably much easier to propose something with a sample file available

Thank you. I am attaching. If you run the macro (only macro in sheet) It does exactly what I want. EXCEPT on the last worksheet Column E & F instead of

Please Login or Register  to view this content.

I would like the formula to read

Please Login or Register  to view this content.

Using different range for each group

This is what I have:

Please Login or Register  to view this content.

It is working perfectly within my Test spreadsheet but copied into my whow worksheet I am getting object variable not set on this row:

Please Login or Register  to view this content.

I have Dim VarRow As Long and Dim VarRange As Range

Got it working.