Subscript out of range/ application defined or object defined error VBA

Question

I asked a similar question a while ago and thought I had fixed my problem but for some reason I tried to run it later on and the same error popped up even though it was fine earlier. Anyway I either get a subscript out of range/ application defined or object defined error when I try and run this code. I am trying to find the cell in a sheet that has the words "current assets:". Here is my code. I feel like the problem is either in the 'range' that I am using 'find' on.

Sub Valuation()
   Dim LastRow As Long
        LastRow = Worksheets("Sheet1").Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Row

   Dim LastColumn As Long
        LastColumn = Worksheets("Sheet1").Range("A1").SpecialCells(XlCellType.xlCellTypeLastCell).Column
   Dim crntassone As Range

   Set crntassone = Worksheets("Sheet1").Range(Cells(1, 1), Cells(LastRow, LastColumn)).Find(What:="Current assets:", After:=Worksheets("Sheet1").Range(Cells(1, 1)), LookIn:=xlValue, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

If anyone sees the problem it would be greatly appreciated.

Which line of code is generating the error'

Cindy Meister
– Cindy Meister

2016-01-26 21:49:54 +00:00
Commented Jan 26, 2016 at 21:49 — Cindy Meister
– Cindy Meister, Commented Jan 26, 2016 at 21:49

Community · Accepted Answer · 2017-05-23 11:59:16Z

1

I believe the issue is you aren't assigning parentage to the Cells(). Try replacing your line:

Set crntassone = Worksheets("Sheet1").Range(Cells(1, 1), Cells(LastRow, LastColumn)).Find(What:="Current assets:", After:=Worksheets("Sheet1").Range(Cells(1, 1)), LookIn:=xlValue, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)

With

With Worksheets("Sheet1")
Set crntassone = .Range(.Cells(1, 1), .Cells(LastRow, LastColumn)).Find(What:="Current assets:", After:=.Range(.Cells(1, 1)), LookIn:=xlValue, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False)
End With

I used With since it saves some space (notice the .s). An alternative is to just add Worksheets("Sheet1") before any Cells() reference (also includes Rows(), Columns() if you used those).

If you don't assign parentage, you'll get an error unless the sheet you're trying to refer to is the ActiveSheet.

edited May 23, 2017 at 11:59

CommunityBot

11 silver badge

answered Jan 26, 2016 at 21:54

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

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

Comments

justkrys · Accepted Answer · 2016-01-26 21:55:53Z

0

I'm not sure you need all that if you just use Set crntassone = Cells.Find(What:="Current assets:", After:=[A1], SearchDirection:=xlPrevious, MatchCase:=False)

answered Jan 26, 2016 at 21:55

justkrys

3003 silver badges13 bronze badges

Collectives™ on Stack Overflow

Subscript out of range/ application defined or object defined error VBA

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related