Removing Duplicate Values-Runtime error 450

[Niraj Baraili]

Hi Experts,

Could you please look into this code why i am getting Runtime error: 450.
wrong number of arguments or invalid property assignment.

Sub Test()

sn = Sheets("sheet1").Cells(1).CurrentRegion

    With CreateObject("Scripting.Dictionary")
   
        For j = 1 To UBound(sn)
            .Item(sn(j, 1)) = Application.Index(sn, j, 0)
       
        Next j
    'write item to worksheet
   
        Sheets("sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.Item, 0, 0)
   
   
    End With
End Sub

 

[Chihiro]

Likely issue is that "Application.Index(sn, j, 0)" is returning array, rather than single element.

So you can't put array directly into a cell and below line is likely causing error.

Sheets("sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.Item, 0, 0)

 

[Niraj Baraili]

Likely issue is that "Application.Index(sn, j, 0)" is returning array, rather than single element.

So you can't put array directly into a cell and below line is likely causing error.

Sheets("sheet1").Cells(1, 4).Resize(.Count, UBound(sn, 2)) = Application.Index(.Item, 0, 0)

Hi Chihiro,

Thanks for your reply.

After lots of googling, found a solution. Just changed this line from Application.Index(.Item, 0, 0) to Application.Index(.Items, 0, 0) and it's working fine.

Thanks again for pointing this out.