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

 

[Norie]

Does the Match work?

 

[Perth40]

Yes, the match works. I put a breakpoint on that line of code and checked the value of ...
Application.WorksheetFunction.Match(Range("E" & k).Value
... and its "Jumbo Operator" as it should be.

 

[Perth40]

I switched off DEBUG and got this message ...

Runtime error '1004'
"Unable to get the Match property of the WorksheetFunction class"

 

[Norie]

Did you check the whole Match formula?

Application.WorksheetFunction.Match(Range("E" & k).Value, Sheet36.Range("C:C"),0)

 

[Perth40]

This portion of code ...
Range("E" & k).Value
... returns
"Jumbo Operator", as it should.

This portion of code ...
Sheet36.Range("C:C")
... says
"Sheet36.Range("C:C") = ******** variable or With block variable not set>

What does that mean?

 

[Perth40]

I dont know whay all those ******* appeared then, but it should say the word "Object".

 

[Norie]

Is there a worksheet with the CodeName Sheet36?

 

[Perth40]

Yes, Sheet36 has text name "Dayworks".

It has 6 columns:
DWID, SiteID, InvoiceItem, DWResource, Units, Rate

With values such as:
1, 1, DL.1, Jumbo Operator, hour, 130
2, 1, DL.2, Loader Operator, hour, 75
3, 1, DL.3, Charge Up Operator, hour, 70

The user picked "Jumbo Operator" from a populated dropdown box in Sheet35 (Dayworks Form), and I'm searching Sheet36 (Dayworks) to find the Description in column 4 so I can return the code in column 3 (DL.1) and place it in Sheet41 (Dayworks Data) ... which eventually gets sucked into a database.

 

[Norie]

Instead of using the CodeName, Sheet36, you could try using Worksheets("DayWorks").