CODEWITHSUNDEEP
vbaexcel
t.mo
t.mo

Reputation: 263

Runtime error 424 Object required

I am unable to find my error in the sub listed below. I receive a "Runtime error 424 Object Required" message.

My intention is to convert my excel formula into VBA code.

Private Sub Workbook_INDEXMATCH()

    Dim wb As Workbook, wb1 As Workbook
    Dim LastRow As Long
    Set wb = Workbooks("WORKBOOK.xlsm")

    With Application.WorksheetFunction
        SHEET1NAME.Range("E13") = _
        .Index(SHEET2NAME.Range("D:D"), .Match(lookup_value, SHEET1NAME.Range("B13"), 0), .Match(lookup_value, SHEET2NAME.Range("A:A"), 0))

    End With
End Sub

Upvotes: 0

Views: 966

Answers (1)

A.S.H
A.S.H

Reputation: 29362

When you're indexing in a single dimension array, it doesn't make sense to have two parameters for the Index.

.Index(SHEET2NAME.Range("D:D"), .Match(lookup_value, SHEET2NAME.Range("A:A"), 0))

Also,

.Match(lookup_value, SHEET1NAME.Range("B13"), 0)

Doesn't make sense because you're searching in a single cell here. May be you meant to search in row 13?

.Match(lookup_value, SHEET1NAME.Range("13:13"), 0)

However, you would use 2 parameters forIndex only if you're indexing in a two-dimensional range. but then you will need to invert the 2nd and 3rd parameters that you gave to Index, because the 2nd param is a row index, which is fetched in a column, and the 3rd is a column index fetched in the row.

For example, suppose you want to find a cell, in range A1:D20 where lookup_value appears on column A (of the same row) and on row 13 (of the same column):

myVariable = .Index(SHEET2NAME.Range("D:D"), _
    .Match(lookup_value, SHEET2NAME.Range("A:A"), 0), _
    .Match(lookup_value, SHEET1NAME.Range("13:13"), 0))

By the way, what are SHEET1NAME and SHEET2NAME? just names or worksheet objects? All of the above supposed they are Worksheet objects, but I guess the runtime error is complaining that these are just names not objects! In this case, replace them everywhere with Worksheets(SHEET1NAME) and Worksheets(SHEET2NAME).

Upvotes: 2

Related Questions