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?

 

[Johnny C]

The data is going into a custom-writted SQL server Db. But the data needs cleansing first before it gets loaded, and some admin tidying up needs doing as the new system can't do write back into the source files.

The source data is all high-volume SAP balance sheet reconcilation items to external data sources, so the source documents need to be in Excel.

 

[Norie]

Johnny

What do you mean here?

referencing a dynamic range on a different worksheet than the one which has the range in

 

[Johnny C]

Hi Norie

I've got a workbook with VBA, which opens other workbooks, SETs the workbook to a workbook object variable, then cycles through the worksheets within it until it finds one with a certain name (with varying capitals/lower case), it then checks items on that worksheet against a list on another worksheet in that workbook using a dynamic range.

Unfortunately the same rangename is used on the workbook with the VBA, however the data in there can be different and the data should not be checked against it, it needs checking against the list in the opened workbook.

VBA gets shirty if I specify the workbook/worksheet/range to check in with .match/.find, so I've had to make sure the opened workbook/worksheet is the active one and use the .match/.find on the rangename with no workbook/worksheet identifier.

 

[RoryA]

VBA gets shirty if I specify the workbook/worksheet/range to check in with .match/.find

only if you did it wrong.

 

[Norie]

Why do you need to use dynamic named ranges in the code?

Are the ranges changing as you run the code so they need constantly updated?

Also, you shouldn't need to activate anything, and that might actually be slowing things down.

 

[Johnny C]

And I don't think I did!

I'll post the relevant bits, tomorrow morning, I'm about to do a runner for the train

 

[Johnny C]

Okay, here's the relevant code.

In the opened workbook there's 2 pages of interest, "Query Log" and LookUps. There can be a number of instances of Query Log with numebrs/letter appended to separate them. I have to cycle through the worksheets in the loop because people change the worksheet name and it could be QUery Log, QUERY LOG, query log, query log 2 ... any combination of upper and lower case with appended numbers/letters.

Dynamic range definition for List_LOB,

=OFFSET(LookUps!$R$1,1,0,COUNTA(LookUps!$R$2:$R$50000),1)

scope is the Query Log sheet, in both the VBA workbook and opened workbook, The data in the two workbooks can vary. When the opened workbook ahs been processed, the contents of the VBA workbook List_LOB are copied to the opened workbook so they have a refreshed list for next month. But the validation checks need to work on the prior version of data in the opened workbook (that was refreshed last month)

Dim wkbkOpened As Workbook
Dim wksDatasheet As Worksheet
 
Workbooks.Open _
     Filename:=rngFileName, _
     UpdateLinks:=False, _
     ReadOnly:=False, _
     Password:=str_AccessPW, _
     writerespassword:=str_RW_Password, _
     IgnoreReadOnlyRecommended:=True, _
     Notify:=False
Set wkbkOpened = ActiveWorkbook
 
For Each wksDatasheet In wkbkOpened.Worksheets
If Left(UCase(Trim(wksDatasheet.Name)),9) = "QUERY LOG" Then
     strRecfileName = wksDatasheet.Name
     wkbkOpened.Sheets(strRecfileName).Select
     If Range("List_LOB").Find(str_LOB, LookIn:=xlValues) Is Nothing Then
          Call LogError(strRecfileName, str_SAP_Account, 0, "LOB on Query Log page is invalid ")
     End If
Next wksDatasheet

which works fine, it finds all occurences of str_LOB in the list.

If however I use the line

     If wkbkOpened.wksDatasheet.Range("List_LOB").Find(str_LOB, LookIn:=xlValues) Is Nothing Then

it doesn't work, it can't find any occurences of str_LOB in the list

Now as far as I can tell (and I may well be wrong, I'm a simple soul) wkbkOpened is a valid object, wksDatasheet is a member of the worksheets property of that object, and List_LOB is a dynamic range on that worksheet.

So, either it doesn't work, or else my understanding is wrong, in which case I'd be gratefuil if you could enlighten me

 

[RoryA]

Now as far as I can tell (and I may well be wrong, I'm a simple soul) wkbkOpened is a valid object, wksDatasheet is a member of the worksheets property of that object, and List_LOB is a dynamic range on that worksheet.

Nope. wbkOpened is a Workbook. wksDatasheet is a variable referring to a specific sheet in that book but wksDataSheet itself is not a property of wbkOpened. (and nor is the range actually on that sheet).

Hence, as I'm pretty sure I've said in your other posts ():

  If wksDatasheet.Names("List_LOB").RefersToRange.Find(str_LOB, LookIn:=xlValues) Is Nothing Then

 

[Johnny C]

Okay, I understand now. Cheers