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

 

[rilzniak]

SOLVED

Haha. You've got to be kidding me! I tried that before but another part of the code was wrong so I ended up reverting to my initial (wrong) version and worked forward from there without trying this solution again. Thanks!

I would have thought that qualifying the sheet in the BMws.Range portion would have been enough. Oh well, learned something new.