Unable to get the Match property of the WorksheetFunction class

[BoyBoy]

Hello,
I was trying to run the following code:

Set e = Sheets("Sheet1")
Set i = Sheets("Sheet2")

Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(e.Range("CH" & j))

If e.Range("CH" & j) <> "" Then
d = d + 1
e.Cells(d, 3).Value = "VI"

e.Cells(d, 4).Value = Application.WorksheetFunction.Index(i.Range("F:F"), Application.WorksheetFunction.Match(Cells(j, 84).Value, i.Range("N:N"), 0))
e.Cells(d, 4).Value = e.Cells(d, 4).Value
e.Cells(d, 5).Value = Application.WorksheetFunction.Index(i.Range("E:E"), Application.WorksheetFunction.Match(Cells(j, 84).Value, i.Range("N:N"), 0))
e.Cells(d, 5).Value = e.Cells(d, 5).Value
If e.Cells(d, 8).Value = Application.WorksheetFunction.Index(i.Range("G:G"), Application.WorksheetFunction.Match(Cells(j, 84).Value, i.Range("N:N"), 0)) = "" Then
e.Cells(d, 8).Value = ""
Else: e.Cells(d, 8).Value = Application.WorksheetFunction.Index(i.Range("G:G"), Application.WorksheetFunction.Match(Cells(j, 84).Value, i.Range("N:N"), 0))
e.Cells(d, 8).Value = e.Cells(d, 8).Value
End If
If e.Cells(d, 8).Value = "" Then
e.Cells(d, 10).Value = ""
Else: e.Cells(d, 10).Value = e.Cells(d, 8).Value - Weekday(e.Cells(d, 8).Value, 3) + 2
e.Cells(d, 10).Value = e.Cells(d, 10).Value
End If

End If
j = j + 1
Loop

End Sub

But received this error

Can anyone please advise what's wrong with my Index & Match function above?
Thanks in advance.

Boyboy

 

[MARK858]

What sheet is Cells(j, 84) on and it that sheet the active sheet?

 

[BoyBoy]

What sheet is Cells(j, 84) on and it that sheet the active sheet?

Cells(j, 84) is in Sheet 1

 

[MARK858]

and was Sheet1 the active sheet when the code was run?

 

[BoyBoy]

yes, Sheet1 was a active sheet.

 

[MARK858]

yes, Sheet1 was a active sheet.

Not a active sheet, there can be only one active sheet. Was it the ActiveSheet?

 

[BoyBoy]

Yes

 

[MARK858]

Ok, what happens with code below? do you get the same error?

Sub ddddd()
    Set e = Sheets("Sheet1")
    Set i = Sheets("Sheet2")

    Dim d
    Dim j
    d = 1
    j = 2

    Do Until IsEmpty(e.Range("CH" & j))

        If e.Range("CH" & j) <> "" Then
            d = d + 1
            e.Cells(d, 3).Value = "VI"

            e.Cells(d, 4).Value = Application.WorksheetFunction.Index(i.Range("F:F"), Application.WorksheetFunction.Match(e.Cells(j, 84).Value, i.Range("N:N"), 0))
            e.Cells(d, 4).Value = e.Cells(d, 4).Value
            e.Cells(d, 5).Value = Application.WorksheetFunction.Index(i.Range("E:E"), Application.WorksheetFunction.Match(e.Cells(j, 84).Value, i.Range("N:N"), 0))
            e.Cells(d, 5).Value = e.Cells(d, 5).Value
            If e.Cells(d, 8).Value = Application.WorksheetFunction.Index(i.Range("G:G"), Application.WorksheetFunction.Match(e.Cells(j, 84).Value, i.Range("N:N"), 0)) = "" Then
                e.Cells(d, 8).Value = ""
            Else: e.Cells(d, 8).Value = Application.WorksheetFunction.Index(i.Range("G:G"), Application.WorksheetFunction.Match(e.Cells(j, 84).Value, i.Range("N:N"), 0))
                e.Cells(d, 8).Value = e.Cells(d, 8).Value
            End If
            If e.Cells(d, 8).Value = "" Then
                e.Cells(d, 10).Value = ""
            Else: e.Cells(d, 10).Value = e.Cells(d, 8).Value - Weekday(e.Cells(d, 8).Value, 3) + 2
                e.Cells(d, 10).Value = e.Cells(d, 10).Value
            End If

        End If
        j = j + 1
    Loop

End Sub

 

[BoyBoy]

Yes, I am still getting the same error

 

[MARK858]

Is it on the first loop?