CODEWITHSUNDEEP
excelvba
Joey L
Joey L

Reputation: 13

Error Object Required for Match function in VBA

I am having some trouble with this code, which I am getting

'Object Required'

error, when I want to use the match/vlookup function. Researched and tried by adding Application.WorksheetFunction but still not working.

Please help me!

Sub SkipIfNoUpdate()

Dim updateneeded As Boolean
    'updateneeded = True
    
    Dim sht As Worksheet
    Set sht = Worksheets("Capacity_Offer_Trial")
    
    Dim LastRowCap As Long
    LastRowCap = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
    
    Dim ThisApp As Application
    
    Set ThisApp = CreateObject("Excel.Application")
    
    Dim i As Integer
    i = 4
    Do While i < LastRowCap + 1
        Cells(i, 4).Select
        If IsError(Application.WorksheetFunction.Match(Cells(i, 4), Subcon!C1, 0)) = True Then
            MsgBox "APPLY FORMULA TO THIS ROW"
        Else
            MsgBox "Do nothing for this row"
        
        End If
        i = i + 1
    Loop


End Sub

Upvotes: 1

Views: 893

Answers (2)

Tim Williams
Tim Williams

Reputation: 166126

Try this:

Sub SkipIfNoUpdate()
    
    Dim wb As Workbook, sht As Worksheet, i As Long, LastRowCap as Long

    Set wb = ThisWorkbook   'or ActiveWorkbook
    Set sht = wb.Worksheets("Capacity_Offer_Trial") 
    
    LastRowCap = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
     
    For i = 4 to LastRowCap
    
        If IsError(Application.Match(ws.Cells(i, 4), _
                           wb.Sheets("Subcon").Columns(1), 0)) Then
            MsgBox "APPLY FORMULA TO THIS ROW"
        Else
            MsgBox "Do nothing for this row"
        End If
    Next i

End Sub

Upvotes: 1

Tomasz
Tomasz

Reputation: 426

You code return error because that Subcon!C1, 0 should be object variable. i delete line with creating Excel object.

Sub SkipIfNoUpdate()

    Dim updateneeded As Boolean
    'updateneeded = True
    
    Dim sht As Worksheet:Set sht = Worksheets("Capacity_Offer_Trial")
    Dim sht2 As Worksheet: Set sht2 = Worksheets("Subcon")
    Dim LastRowCap As Long: LastRowCap = sht.Cells(sht.Rows.Count, "D").End(xlUp).Row
    Dim i As Integer:i = 4
    
    Do While i < LastRowCap + 1
        sht.Cells(i, 4).Select
        If IsError(Application.WorksheetFunction.Match(sht.Cells(i, 4), sht2.cells(1,3), 0)) = True Then
            MsgBox "APPLY FORMULA TO THIS ROW"
        Else
            MsgBox "Do nothing for this row"
        
        End If
        i = i + 1
    Loop


End Sub

but try use range.find method like example below

set findrange = sht2.cells.find(wanted) ' lookup whole sheet
if findrange is nothing then 'if not find

Upvotes: 1

Related Questions