CODEWITHSUNDEEP
excelvba
Drew Phillips
Drew Phillips

Reputation: 3

application defined or object defined error - Excel VBA .range(cells

I have a line of code that is displaying the application defined or object defined error, I have no idea why. 

filenm17 = "DSR Consolidated"
findstring16 = "21"

filenm17 and findstring16 are both defined as strings. I tried removing findstring16 and replacing it with the formula to get findstring16 (it's a cell reference .value) but that still didn't work.

Workbooks(filenm17).Sheets("5").Range(Cells(67, findstring16 + 2), _
                               Cells(440, findstring16 + 2)) = firstarray

Please help!

Upvotes: 0

Views: 110

Answers (1)

Tim Williams
Tim Williams

Reputation: 166306

Without a qualifying worksheet, your Cells() will refer to the Activesheet: if that isn't "5" then they don't match the Range() and that will trigger the error you're getting.

Try:

With Workbooks(filenm17).Sheets("5")
    .Range(.Cells(67, CLng(findstring16) + 2), _
           .Cells(440, CLng(findstring16) + 2)).Value = firstarray
End With

Upvotes: 2

Related Questions