Error: unable to get the match property of the worksheet function class

[punno]

Dear Good People,

I am having this error message: unable to get the match property of the worksheet function class. Below is my code where iam getting These Problem. Let me know what should i corret.Thanks

For i = 15 To 1000
If Worksheets("Sheet1").Cells(0 + i, 6) > 3 Then
'Worksheets("Sheet1").Cells(0 + i, 7) = "=INDEX('Known issues'!$A$3:$H$1000,MATCH('Known issues'!H28,'Known issues'!$F$3:$F$10,0),3)"
Worksheets("Shet1").Cells(0 + i, 7).Value = Application.WorksheetFunction.Index(Sheets("Known issues").Range("$A$3:$H$1000"), Application.WorksheetFunction.Match(Sheets("Known issues").Cells(i - 12, 8).Value, Sheets("Known issues").Range("$F$3:$F$10"), 0), 3)
End If
Next i




Best Regards,
Punno

 

[jim may]

Welcome to Mr Excellll

Revisit this part of your code line...

Worksheets("Shet1")

 

[punno]

Hi,
Thaks for the reply. That was mistake while posting. The roblem is still there. Let me post again:

For i = 15 To 1000
If Worksheets("Sheet1").Cells(0 + i, 6) > 3 Then

Worksheets("Sheet1").Cells(0 + i, 7).Value = Application.WorksheetFunction.Index(Sheets("Known issues").Range("$A$3:$H$1000"), Application.WorksheetFunction.Match(Sheets("Known issues").Cells(i - 12, 8).Value, Sheets("Known issues").Range("$F$3:$F$10"), 0), 3)

 

[higrm]

I took your line out of the loop and it works for me, so there may be something wrong elsewhere in your code.

Sub test1()
Worksheets("Sheet1").Cells(15, 7).Value = Application.WorksheetFunction.Index(Sheets("Known issues").Range("$A$3:$H$1000"), Application.WorksheetFunction.Match(Sheets("Known issues").Cells(15 - 12, 8).Value, Sheets("Known issues").Range("$F$3:$F$10"), 0), 3)
End Sub

 

[Fluff]

That error means that the value in

Cells(i - 12, 8)

cannot be found in the lookup range

 

[punno]

That error means that the value in

Cells(i - 12, 8)

cannot be found in the lookup range

Actually it is in the range. And it gives me the right result but then it gives me this error message. Not sure where i went erong

 

[Fluff]

If it gave you the right result, then you would not have got an error.
Check the value in that cell after you get the error.

 

[punno]

If it gave you the right result, then you would not have got an error.
Check the value in that cell after you get the error.

It was supposed to pickup some comments for specific condition. But after it put the comment for the condition in a cell , it cannot put the comment for other cell where the condition met.

 

[Fluff]

What is the value of i when the code fails?

 

[punno]

What is the value of i when the code fails?

It doesnt give any value , completely blank.i wanted to use the for Loop thats why i used worksheet function to use cells.