index match function vs index match vba type mismatch

=INDEX('SGV OPC Codes'!B:B,MATCH(1,(Finished!D2='SGV OPC Codes'!C:C)*(Finished!E2='SGV OPC Codes'!D:D)*(Finished!H2='SGV OPC Codes'!H:H)*(Finished!I2='SGV OPC Codes'!E:E),0))
the formula above works perfectly fine. but when I convert it to vba I am getting type mismatch.
I have tried :

Please Login or Register  to view this content.

and

Please Login or Register  to view this content.

any idea?

Where you have indexRng, maybe try indexRng.Address. Same for the other xxxRng variables.

And perhaps change finWs.Range("D" & x) to finWs.Range("D" & x).Address.

Just guesses, but something to try.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file.

Ok. Late now. I'll have a look tomorrow if no-one else has intervened and offered a solution.

Sorry, saw another similar thread and I thought it was this.

I would be inclined to go with the formula you have rather than convert to VBA.

Try this ... you end up with values anyway:

Please Login or Register  to view this content.

I was trying to make it quicker by not using full columns but, for some reason, it won't play. I'll have another look tomorrow.

OK, the code is a bit, alright, a lot longer, but it executes much more quickly.

Please Login or Register  to view this content.

In reality, the Dynamic Named Ranges are static Named Ranges. However, they are dynamic in the sense that they will be updated every time the Change Event handler runs, so they will always reflect the current state.

Is this resolved now?

You're welcome.


An interesting challenge



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.