|re: Before it executes it reads 'SkuLoc=0'.|
Obviously, you are using this line in a much longer macro than the example I posted, so SkuLoc = 0 may not be relevant at this point.
re: Match or any other lookup function does not appear in the drop down box after 'Application'
I believe that's normal. Try it after:
But seeing Match in the list won't solve your problem
As I mentioned earlier, when I run the simple code that I posted, the only time I get a Type Mismatch is when the lookup_value can't be found in the lookup_array.
The reason for this is that the Match function is returning an Error 2042 (#N/A) and you can't subtract 1 from an error. The types - a numerical value (1) and an error (Error 2042) - don't match, thus you get a Type Mismatch when you try to perform the mathematical operation.
I can prove this to myself by eliminating the -1 from the line, using F8 to single step through the code and looking at SkuLoc in the Watch Window immediately after the line executes - which it does without the -1. The value for SkuLoc in that case is Error 2042 which equates to a #N/A error, the same error you would get if the lookup_value can't not be found in the lookup_array when using the MATCH function in a cell.
Are you 100% sure that the code is looking at the same sheet and range where you expect the lookup_value to be found and that the lookup_value is actually in that range?
Try this test:
Range("A1", Cells(1, LastCol)).Interior.ColorIndex = 4
The same range that your VBA Match function is searching should turn Green. If it's the right range, then try some other value. Manually enter TestData somewhere in that range and use the VBA Match line to find "TestData".
Maybe the conversion corrupted the data and what looks like Sku # really isn't Sku #.
Just tossing out ideas...