Match & Index code results in Runtime error 1004

Hi All

I am trying to write the vba code for the Match & Index functions. My code results in a Runtime Error 1004: Application defined or object defined error.

My code is:

Please Login or Register  to view this content.

I have tested the "Left(Sheets("Journal").Range("C27").Value, 6)" and the "Index" portion of the code with no errors. The error occurs on the "Match" portion of the code.

The variables "TypeTable" and "AccNo" are Named Ranges on the "ChartOfAccounts" worksheet.

I would appreciate it if anyone could please tell me what I am doing wrong.

I have subequently changed the code by removing "WorksheetFunction" - see new code below:

Please Login or Register  to view this content.

This has resolved the Runtime Error 1004, but now I have an Error 13 Type Mismatch error.

Any suggestions will be highly appreciated.

Thank you

It is difficult to diagnose this just by looking at your code. I don't see any obvious errors. Can you attach your file?

First, I would generally suggest going back to Application.WorksheetFunction. Application.Match does not seem to be documented and though I got it to work, I don't know what's the difference between using that vs. Application.WorksheetFunction.Match. It may not be causing your problem but I would minimize the number of variables here.

One strategy for debugging this kind of thing is to break the operation into multiple steps so you can see exactly what step is failing. For example, step through this code in the debugger and look at the results at every step:

Please Login or Register  to view this content.