Why the object does not support this method?

user_17528999 0
Viewed 46

I have the following code

Dim Shop1 As String
With Worksheets("Data Page")
Shop1 = Application.WorksheetFunction.Index(.Range("B2:B7"), Application.Worksheet.Match(profitable1, .Range("E2:E7"), 0))
End With

enter image description here

This is how my excel looks like

The code works at first, but after I change some value,it suddenly does not and the above error pops out. Can someone tell me why? Thank you so much for helping

properties vba excel
1 Answers

Application.Match vs WorksheetFunction.Match (WorksheetFunction.Index)

  • You have misspelled WorksheetFunction in your code as mentioned by Raymond Wu in the comments: Worksheet.Match should be WorksheetFunction.Match.
  • The early-bound WorksheetFunction version of Match will raise an error if a value is not found so you will have to implement some kind of error handling. The late-bound Application version is preferred because it can be tested with IsNumeric or IsError.
  • I can't recall using Index/Match in VBA. It is usually handled as illustrated in the following code.
Option Explicit

Sub Test()
    
    Dim sIndex As Variant ' a number or an error value, hence 'As Variant'
    Dim profitable1 ' ?
    Dim Shop1 As String ' this actually means Shop1 = ""
    
    With ThisWorkbook.Worksheets("Data Page")
        With .Range("E2:E7")
            sIndex = Application.Match(profitable1, .Cells, 0)
            If IsNumeric(sIndex) Then
                Shop1 = CStr(.Cells(sIndex).EntireRow.Columns("B").Value)
                ' Or:
                'Shop1 = CStr(.Cells(sIndex).Offset(, -3).Value)
            'Else ' if the code is in a loop
            '    Shop1 = ""
            End If
        End With
    End With

End Sub
  • It becomes simpler (more readable) when using range variables.
Sub Test2()
    
    Dim sIndex As Variant ' a number or an error value, hence 'As Variant'
    Dim profitable1 ' ?
    Dim Shop1 As String ' this actually means Shop1 = ""
    
    Dim lrg As Range ' Lookup
    Dim vrg As Range ' Value
    
    With ThisWorkbook.Worksheets("Data Page")
        Set lrg = .Range("E2:E7")
        Set vrg = .Range("B2:B7")
    End With
    
    sIndex = Application.Match(profitable1, lrg, 0)
    If IsNumeric(sIndex) Then
        Shop1 = CStr(vrg.Cells(sIndex).Value)
    'Else ' if the code is in a loop
    '    Shop1 = ""
    End If

End Sub
user_98140690
Related