worksheetfunction.match problem

[Johnny C]

Hi

I've got some VBA which is scanning a list of data on a number of files for errors.

Each workbook has a number of pages, including 1 called 'LookUps' and 1 called 'Query log'

The Query log page has a dynamic range, List_LOB which is defined by
List_LOB=OFFSET(LookUps!$R$1,1,0,COUNTA(LookUps!$R$2:$R$50000),1) in the name manager.

When I pick up a cell from the Query log sheet (into variable str_LOB) and try and match it (to see if it's a valid entry in the list in the Lookups sheet) VBA can't find a match. the value it's looking for is there, it's never matching anything.

lngLOBIndex = WorksheetFunction.Match(str_LOB, wkbkOpened.wksdatasheet.Range("List_LOB"), 0)

(wksdatasheet is the Query log sheet, and as I said the range List_LOB is defined in Names manager on the Query log page)

If I do (to test the range)

wkbkOpened.wksdatasheet.Range("List_LOB").select
With Selection.Font
     .Color = -16711681
     .TintAndShade = 0
End With

then it ignores the range completely and colours the font on the active cell on the query log sheet yellow. I don't know if that's an invalid test or not. If I use the dynamic range in a formula on the worksheet in the Query log sheet, it works fine.

Can I access a range on an unselected sheet (which is hidden too) defined by a dynamic range using worksheetfunction.match?

 

[JoeMo]

One problem I see is that the way you have defined List_LOB it is not a named range (e.g., if you pull down the name box on your worksheet you will not find it there). You would be better off defining this dynamic range in your code using VBA's offset function and WorksheetFunction.Counta ...

 

[Johnny C]

It needs to be in the sheet for the data validation to work Joe. I could have two ranges for the same thing but incline to Occam's razor

On another board someone suggested using .Find. I'll test it out, to see if that's quicker, probably a lot less finicky that .match too.

 

[RoryA]

Looking at your code, I'm guessing that you have some sort of error handler suppressing errors (or you just aren't telling us about the errors).

lngLOBIndex = WorksheetFunction.Match(str_LOB, wksdatasheet.Names("List_LOB").Referstorange, 0)

Note: my personal preference is to use Application.Match and assign the result to a variant which you can then test with IsError to see if a match was found. Your way will cause a RTE which you need to trap with an error handler.

 

[Johnny C]

I'm error trapping left, right and centre.

I didn't know you could use application.match, doesn't come up in the help file on my machine (I say that because they monkey around with those sort of things here and it may be incomplete).

What's the difference between application.match and worksheetfunction.match? Does application.match return a double type?

I tried it and it works. I use a on error resume next before and test the value of the var that takes the value of the application.match, and it returns 0 if it can't find it or the location if it does. So I can test either err.number or the variable=0.

 

[RoryA]

Mainly that the former returns an error value if there is no match whereas the latter raises a run time error.

 

[Norie]

Are there definitely matches?

What type of data are you searching and what are you looking for?

Have you considered using VBA Find instead of Match?

Might not help if there aren't any matches but it might be easier to work with when there isn't one.

 

[Johnny C]

Hi Norie

I'm going to test .Find - but only to see if it's noticably faster.

The code currently takes 2hours+ to run on a good day - it runs across 2,000 workbooks with upto 30K rows in a particular sheet with data that needs testing to see if it's in a list of upto 4000 items in 5 of the columns, approx 750 lines of VBA excluding comments/blanks - so any efficiencies on things like match will be useful

I don't need to know where it is, just if it's there so .find would be fine to use.

 

[Norie]

Don't know about speed, and even if there was a difference it might be negligible.

With that many workbooks and that amount of data there's probably not much you can do to make things faster, as far as I can see anyway.

Personally I'd be tempted to transfer all the data into something like Access, if that was possible of course.

 

[Johnny C]

.find works fine, but still has the limitations of referencing a dynamic range on a different worksheet than the one which has the range in. it would avoid the need for a few error traps though.

I'll do some timing on different versions when I get some time.