Problem with Application.WorksheetFunction.Index

[Perth40]

Hi,

I'm making changes to a pre-existing workbook with lots of VB in it.

Three (3) of my INDEX functions work, but the 4th one fails, and I cannot work out why. Please help!


' This works fine
' Add in Resource
ResID = Application.WorksheetFunction.Index(Sheet26.Range("A:A"), Application.WorksheetFunction.Match(Range("Data_Entrant").Value, Sheet26.Range("AA:AA"), 0))

Sheet41.Range("B" & i).Value = ResID


' This works fine
' Add in MineID
MineID = Application.WorksheetFunction.Index(Sheet24.Range("A:A"), Application.WorksheetFunction.Match(Range("Dayworks_Mine").Value, Sheet24.Range("C:C"), 0))

Sheet41.Range("C" & i).Value = MineID


' This works fine
' Add in SiteID
SiteID = Application.WorksheetFunction.Index(Sheet25.Range("A:A"), Application.WorksheetFunction.Match(Range("site").Value, Sheet25.Range("B:B"), 0))

Sheet41.Range("D" & i).Value = SiteID


' THIS ONE FAILS, AND I CANNOT UNDERSTAND WHY
' Add in DWResource
DWResID = Application.WorksheetFunction.Index(Sheet36.Range("D:D"), Application.WorksheetFunction.Match(Range("E" & k).Value, Sheet36.Range("C:C"), 0))

Sheet41.Range("I" & i).Value = DWResID

 

[Perth40]

DWResID = Application.WorksheetFunction.Index(Worksheets("DayWorks").Range("D:D"), Application.WorksheetFunction.Match(Range("E" & k).Value, Worksheets("DayWorks").Range("C:C"), 0))

Got same error ...

"Unable to get the Match property of the WorksheetFunction class" <!-- / message -->

 

[Norie]

Does the Match work when you use it in a cell?

 

[Perth40]

If I put this ion a cell on Sheet35 or Sheet41 ...
=Application.WorksheetFunction.Match(Range("E8"), Sheet36.Range("C:C"), 0)

I get "#NAME?" in the cell.

 

[shg]

=Match(E8, 'Sheet36'!C:C, 0)

... after changing Sheet36 to whatever is the sheet name as it appears on the tab

 

[Perth40]

=MATCH(E8, Dayworks!C:C, 0)
returns "#N/A"




BTW, my first three work ...
' Add in Resource
ResID = Application.WorksheetFunction.Index(Sheet26.Range("A:A"), Application.WorksheetFunction.Match(Range("Data_Entrant").Value, Sheet26.Range("AA:AA"), 0))
Sheet41.Range("B" & i).Value = ResID

' Add in MineID
MineID = Application.WorksheetFunction.Index(Sheet24.Range("A:A"), Application.WorksheetFunction.Match(Range("Dayworks_Mine").Value, Sheet24.Range("C:C"), 0))
Sheet41.Range("C" & i).Value = MineID

' Add in SiteID
SiteID = Application.WorksheetFunction.Index(Sheet25.Range("A:A"), Application.WorksheetFunction.Match(Range("site").Value, Sheet25.Range("B:B"), 0))
Sheet41.Range("D" & i).Value = SiteID

 

[Norie]

In those three you've used named ranges.

In the one that doesn't work you've used Range("E"& K).Value, without a worksheet reference.

 

[Perth40]

I just tried these variations, but neither worked.


DWResID = Application.WorksheetFunction.Index(Sheet36.Range("D:D"), Application.WorksheetFunction.Match(Sheet35.Range("E" & k), Sheet36.Range("C:C"), 0))


DWResID = Application.WorksheetFunction.Index(Worksheets("DayWorks").Range("D:D"), Application.WorksheetFunction.Match(Worksheets("DayWorks Form").Range("E" & k), Worksheets("DayWorks").Range("C:C"), 0))

 

[Norie]

How about the Match not working because there is no match?

That could be because of something as simple as an errant space in the value you are trying to match or in the range you are trying to match it with.

 

[Perth40]

I hunted around on the WWW and found an alternative, which works. I created a named range on the lookup tab and used the VLooKUp function.

DWResID = Application.WorksheetFunction.VLookup(Range("E" & k), Sheet36.Range("Dayworks_resource_lookup"), 4, False)

Sheet41.Range("I" & i).Value = DWResID


Its not my preferred option, but I've been looking at this for 2 days now and its not worth spending any more time on.

Thank you for your interest and suggestions.

Perth40

 

[Norie]

What were you looking up/matching?

Was it really 'Jumbo Operator'?