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]

Thats not 100% clear.

When I clicked on the cell that contained the text and copied it to notepad to look at it, I got "Jumbo Operator ", a couple of spaces on the end.

However, when I looked at the "myValue" in the module that retrieved the text, it was "Jumbo Operator", with no spaces.

 

[Norie]

That could be the problem there.

Like I said even something like an errant space could cause the Match not to work.

 

[Perth40]

I agree, but I did use the TRIM function at one point to eliminate the possibility of any extra spaces, and it made not difference, the error still occurred.

 

[Norie]

Did you use TRIM with the value you were trying to match or the values you were trying to match it with?

 

[rilzniak]

I've gone through several posts like this one but still can't figure out why my code trips at the 'Application.WorksheetFunction' line. Can anyone tell me what I'm doing wrong here? Sure hope it's not something stupid I haven't noticed (but wouldn't be surprised if it was). Thanks in advance.

Dim LastUsedColumn as Long, LastUsedRow as Long
Dim BMws as Worksheet, ws as Worksheet

Set ws = ActiveSheet
Set BMws = Worksheets("BranchMaster")

    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastUsedRow = 1
    Else
        LastUsedRow = ws.Cells.Find(What:="*", _
                      After:=ws.Range("A1"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    End If
    

    If Application.WorksheetFunction.CountA(ws.Cells) = 0 Then
        LastUsedColumn = 1
    Else
        LastUsedColumn = ws.Cells.Find(What:="*", _
                         After:=ws.Range("A1"), _
                         Lookat:=xlPart, _
                         LookIn:=xlFormulas, _
                         SearchOrder:=xlByColumns, _
                         SearchDirection:=xlPrevious, _
                         MatchCase:=False).Column
    End If

ws.Activate
    
Extract = InputBox("Extract Financial Reporting Branch (f) or Customer Number (c)?")


For i = 2 To LastUsedRow


    Select Case Extract
        
        Case "f"
            Cells(1, LastUsedColumn + 1).Value = "BranchNo"
            Cells(i, LastUsedColumn + 1) = left(Cells(i, FRBCol), 3)
            Cells(1, LastUsedColumn + 2).Value = "BranchName"

Cells(i, LastUsedColumn + 2).Value = Application.WorksheetFunction.Index(BMws.Range("H2:H339"), _
                                      Application.WorksheetFunction.Match(ws.Range("E2"), BMws.Range("A2:A339"), 0), 1)

        Case "c"
            Cells(i, LastUsedColumn + 1) = left(Cells(i, CustomerCol).Value, InStr(Cells(i, CustomerCol).Value, " ") - 1)
            Cells(1, LastUsedColumn + 1).Value = "CustomerNo"
                  
    End Select


Next i

 

[Peter_SSs]

.. my code trips at the 'Application.WorksheetFunction' line.

    Select Case Extract
        
        Case "f"
            Cells(1, LastUsedColumn + 1).Value = "BranchNo"
            Cells(i, LastUsedColumn + 1) = left(Cells(i, [COLOR="#FF0000"][B]FRBCol[/B][/COLOR]), 3)
            Cells(1, LastUsedColumn + 2).Value = "BranchName"

"my code trips" What does that actually mean?

.. And which Application.WprksheetFunction are you talking about?

The obvious failure point that I can see (can't really test as I don't have your data) is highlighted above. You have used a variable that is un-dimensioned and has no value set. That would cause an error on that line.

 

[rilzniak]

I get the following error message: "Run-time error 1004: Unable to get the Match property of the WorksheetFunction class" and the Visual Basic dialog box that tells me to either "End" or "Debug" the code.

 

[RoryA]

The most likely cause is that your match value isn’t being found. If it’s numeric, it’s usually because either the lookup value is actually text and the values in the lookup table are real numbers, or vice versa.

 

[rilzniak]

So...I've worked it out to the Match function not performing as expected. Here,s what I mean:

The code below works.

Application.WorksheetFunction.Match(left(Cells(i, FRBCol), 3), BMws.Range("A2:A" & LastUsedBMRow), 0)

The code below does not.

Application.WorksheetFunction.Match(left(Cells(i, FRBCol), 3), BMws.Range(cells(2,1),cells(LastUsedBMRow,1)), 0)

Why is that?

 

[RoryA]

You haven’t qualified the Cells properties:

Application.WorksheetFunction.Match(left(Cells(i, FRBCol), 3), BMws.Range(BMWs.cells(2,1),bmws.cells(LastUsedBMRow,1)), 0)