application defined or object defined error whey copying data into array

Question

I am used to assigning ranges to arrays. However, for some reason right now I am constantly getting an application or object defined error (the first code line below). The second line below works fine. It is identical to the first line except I just copy the range showing all the variables exist.

I have checked in the watch window, arrActuals is Variant/Variant(). Adding .Value at the end of the first line did not solve the error either. Any ideas on why this is happening?

  arrActuals = wkbOVHFile.Sheets(szAOPPage).Range(Cells(iStartCopy, IntActOVHCol), Cells(iEndCopy, IntActOVHCol))
                                            
wkbOVHFile.Sheets(szAOPPage).Range(Cells(iStartCopy, IntActOVHCol), Cells(iEndCopy, IntActOVHCol)).Copy

Try arrActuals = wkbOVHFile.Sheets(szAOPPage).Range(wkbOVHFile.Sheets(szAOPPage).Cells(iStartCopy, IntActOVHCol), wkbOVHFile.Sheets(szAOPPage).Cells(iEndCopy, IntActOVHCol)).Value. To avoid such long lines (improve readability), rather use variables e.g.: Dim ws As Worksheet: Set ws = wkbOVHFile.Sheets(szAOPPage) and then do arrActuals = ws.Range(ws.Cells(iStartCopy, IntActOVHCol), ws.Cells(iEndCopy, IntActOVHCol)).Value. — VBasic2008
– VBasic2008, Commented Nov 13, 2021 at 2:04

Tim Williams · Accepted Answer · 2021-11-13 02:03:02Z

1

Cells without a qualifying worksheet object defaults to the active sheet, so your code fails when wkbOVHFile.Sheets(szAOPPage) is not the active sheet.

More robust like this:

Dim rng As Range

With wkbOVHFile.Sheets(szAOPPage)
    Set rng = .Range(.Cells(iStartCopy, IntActOVHCol), _
                     .Cells(iEndCopy, IntActOVHCol))
End With

arrActuals = rng.Value
rng.Copy

answered Nov 13, 2021 at 2:03

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

application defined or object defined error whey copying data into array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related