vba ERROR 1004 method range of object_worksheet failed

[anand3dinesh]

Hi All,

I have below code to assign range of cells value into Arr variable.
where Arr is variant, not sure what causing "vba ERROR 1004 method range of object_worksheet failed".
could you please help me to fix this?

Thanks in advance, Please see below code. error is in red line below

Dim wKPI As Workbook
Dim wKPIsh As Worksheet
Dim Arr as Variant

For Each wKPIsh In wKPI.Worksheets
cKPI_Lcol = cKPIsh.Range("XFD7").End(xlToLeft).Column
If cKPIsh.Name = wKPIsh.Name Then
'Declare Last row variables
wKPI_Lrow = wKPIsh.Range("B1048576").End(xlUp).Row
wKPI_Lcol = wKPIsh.Range("XFD7").End(xlToLeft).Column

For wKPIr = 8 To wKPI_Lcol
Arr = wKPIsh.Range(Cells(wKPIr, 2), Cells(wKPIr, wKPI_Lcol)).Value

 

[Fluff]

It needs to be like

Arr = wKPIsh.Range(wKPIsh.Cells(wKPIr, 2), wKPIsh.Cells(wKPIr, wKPI_Lcol)).Value

 

[Alex Blakenburg]

Once you fix that, you might also want to look at your For Loop, it seems to be cycling through rows but it using the Last Column where you would expect the Last Row No.

For wKPIr = 8 To wKPI_Lcol
'Shouldn't this be
For wKPIr = 8 To wKPI_Lrow

 

[anand3dinesh]

It needs to be like

Arr = wKPIsh.Range(wKPIsh.Cells(wKPIr, 2), wKPIsh.Cells(wKPIr, wKPI_Lcol)).Value

you are a star, thanks mate

 

[Fluff]

Glad we could help & thanks for the feedback.
As Alex has said, it looks as though your loop is wrong.

 

[anand3dinesh]

Once you fix that, you might also want to look at your For Loop, it seems to be cycling through rows but it using the Last Column where you would expect the Last Row No.

For wKPIr = 8 To wKPI_Lcol
'Shouldn't this be
For wKPIr = 8 To wKPI_Lrow

oh yeah, you are right. Thanks mate