Worksheetfunction Large doesn't work always

[ER_Neha]

Hello friends,

I have a query that, suppose I have two sheets named - SHEET1 & SHEET2

I write a code for sheet1 that contains the raw data , which includes worksheetfunction like LARGE

But i want the large numbers obtained from sheet1 to be extracted to sheet2

While I run the code , if i have opened my sheet2 , and run the code it shows me a run time error 1004 stating that unable to get the large property of the worksheet function class.

but the same code runs well while i have my sheet1 opened , it provides me the large numbers in the sheet2, with no error at all.

Kindly provide me with the detail, why it happens, I will be glad to learn.

 

[Fluff]

Difficult to say for certain, without seeing the code, but I suspect you haven't qualified the range with the sheet name.

 

[ER_Neha]

Hello Fluff, Please I am attaching the code, your explanation will be a great help. Also I am attaching the screenshot of the error, which i am getting if i run the code while i have opened the sheet2

Sub Macro()
Dim i As Double
Dim j As Double

Dim od As Worksheet
Dim nw As Worksheet

Set od = Sheets("Sheet1")
Set nw = Sheets("Sheet2")

For i = 1 To 5
    j = od.Application.WorksheetFunction.Match(od.Application.WorksheetFunction.Large(Range("b2:b20"), i), Range("b2:b20"), 0)
    nw.Range("a" & i & ":" & "b" & i).Value = od.Range("a" & j + 1 & ":" & "b" & j + 1).Value
Next i

End Sub

 

[Fluff]

You haven't qualified the ranges, it should be

    j = Application.WorksheetFunction.Match(Application.WorksheetFunction.Large(od.Range("b2:b20"), i), od.Range("b2:b20"), 0)

 

[ER_Neha]

Thank u so much. It worked

You haven't qualified the ranges, it should be

    j = Application.WorksheetFunction.Match(Application.WorksheetFunction.Large(od.Range("b2:b20"), i), od.Range("b2:b20"), 0)

 

[Fluff]

You're welcome & thanks for the feedback.