VBA : Unable to get the Match property of WorkSheetFunction class

[kera404]

I have a list of Names in the sheet "ClaimAnalysis", and a subset of these Name Values in the sheet "Analysis". To select the Names that have values in "Analysis1", I used Index-Match function for each value in the column of "ClaimAnalysis" (frow is the count of total rows in "ClaimAnalysis"). The formula I am applying works fine manually Formula Bar but throws 'runtime error in VBA.

Formula Bar "=INDEX(Analysis1!$B$2:$B$717,MATCH(ClaimAnalysis!A2,Analysis2!$A$2:$A$727,0))"

and yes variable frow makes my range as per desired in formula bar

For x = 2 To frow - 1        ThisWorkbook.Sheets("ClaimAnalysis").Cells(x, 2).Value = _
            Application.WorksheetFunction.Index(Sheets("Analysis1").Range("B2").End(xlDown), Application.WorksheetFunction.Match(ThisWorkbook.Sheets("ClaimAnalysis").Cells(x, 1), Sheets("Analysis1").Range("A2").End(xlDown), 0))
    Next x

 

[yky]

Try this:
[CODE​]ThisWorkbook.Sheets("Analysis1").Range("A2").End(xlDown)[/CODE]

 

[wideboydixon]

Sheets("Analysis1").Range("A2").End(xlDown)

This returns a single cell and not a lookup range.

WBD