Match funtion ceased working with 2003 upgrad

Microsoft Excel 2003 (full product)
September 8, 2009 at 11:25:56
Specs: XP SP2
Our IT guy updated my machine to XL2003 and the match function in a macro stopped working. The problem occurs when trying to assign the location to the SkuLoc and QtyLoc variables. It produces a Type Mismatch error. I have searched the internet for some version difference with no success. If I add a formula to a cell using the match function it sees it and the parameters are identical to this code which leaves me confused.

SkuLoc = Application.Match("Sku #", Range("A1", Cells(1, LastCol)), 0) - 1


See More: Match funtion ceased working with 2003 upgrad

Report •


#1
September 8, 2009 at 12:12:34
I'm running Excel 2003 Pro and it works for me.

The only time I get a Type Mismatch is if "Sku #" isn't found in the lookup_array.

e.g. I put Sku # in D1.

With LastCol = 4, this sets SkuLoc to 3:

Sub TestMatch()
 LastCol = 4
  Skuloc = Application.Match("Sku #", Range("A1", Cells(1, LastCol)), 0) - 1
End Sub


With LastCol = 3, I get a Type Match error:

Sub TestMatch()
 LastCol = 3
  Skuloc = Application.Match("Sku #", Range("A1", Cells(1, LastCol)), 0) - 1
End Sub

The reason, AFAICT, is because the Match function didn't return a value if Sku # wasn't found, so you can't subtract 1 from it.

As a test, remove the -1 from your code and hover over SkuLoc after it executes that line.


Report •

#2
September 8, 2009 at 12:56:45
Result is the same. Before it executes it reads 'SkuLoc=0'.

I noticed when retyping the line that Match or any other lookup function does not appear in the drop down box after 'Application." It appears if used in a cell formula but not in a macro. Is there a library or add-in I need to reinstall?


Report •

#3
September 8, 2009 at 14:05:07
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:

Application.WorksheetFunction.

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...


Report •

Related Solutions

#4
September 11, 2009 at 12:13:55
I tried this and more and nothing worked. The only thing that did was colorizing the cells green. I don't know what happened between the two versions but the match function uses the proper syntax and doesn't work anymore on either XL2003 or XL2007. I ended up having our IT guy reinstall XL2000 and everything works again. I would like to figure this out but I have lost a lot of work time. I actually learned a thing to two in this process so thanks for your help.

Report •


Ask Question