Run time error Method 'Range' of Object '_Worksheet' Failed

[neon767]

I have the following code:

Sub Test()

Const CL As String = "A"
Dim wsCount, outRow, bottom As Integer
wsCount = Worksheets.Count - 1
outRow = 13
Dim wsSrc(1 To 14) As Worksheet
Dim wsDest As Worksheet: Set wsDest = Sheets("Search")
wsDest.Range("A13:AV1000").ClearContents
For i = 1 To wsCount
Set wsSrc(i) = Sheets(i)
Dim LR As Long: LR = wsSrc(i).Range(CL
Rows.Count).End(xlUp).Row
With wsSrc(i).Range("A1:AV1048" & LR)
.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wsDest.Range("C1:C2")
CopyToRange:=wsDest.Range("A" & outRow), Unique:=True
End With
bottom = Cells(Rows.Count, "H").End(xlUp).Row
If bottom <= 13 Then
outRow = 13
wsDest.Range("A13:AV1000").ClearContents
Else
GoTo loopBreak
End If
Next i
loopBreak:

End Sub

or some variation of that. Basically, it looks through all the othe
worksheets and copies any data with a matching id # and all the column
in that row. However, I get the above error when
With wsSrc(i).Range("A1:AV1048" & LR)
goes to AV1049 or higher (A1:AV1048 is the highest range it will wor
with). Every one of the other sheets has information in rows tha
significantly exceed row 1048, though. I'm not sure what specificall
about that number could be causing a problem, or what exactly this erro
means about my function

 

[Auric__]

I have the following code: [snip]
or some variation of that. Basically, it looks through all the other
worksheets and copies any data with a matching id # and all the columns
in that row. However, I get the above error when
With wsSrc(i).Range("A1:AV1048" & LR)
goes to AV1049 or higher (A1:AV1048 is the highest range it will work
with). Every one of the other sheets has information in rows that
significantly exceed row 1048, though. I'm not sure what specifically
about that number could be causing a problem, or what exactly this error
means about my function.

Try putting a comma right after the 1048, like this:
With wsSrc(i).Range("A1:AV1048," & LR)

 

[joeu2004]

Try putting a comma right after the 1048, like this:
With wsSrc(i).Range("A1:AV1048," & LR)

To what avail?

Rhetorical question. The point is: LR is type Long in "neon767's" example,
not type Range.

I am not really digging into "neon767's" logic; I see so many other errors
and questionable code.

But with respect to this one line, perhaps the following is what "neon767"
intends:

With wsSrc(i).Range("A1:AV" & LR)

 

[Auric__]

To what avail?

Rhetorical question. The point is: LR is type Long in "neon767's"
example, not type Range.

Totally missed that little detail. That's what I get for posting while tired.

 

[neon767]

Try putting a comma right after the 1048, like this:
With wsSrc(i).Range("A1:AV1048," & LR)-

To what avail?

Rhetorical question. The point is: LR is type Long in "neon767's
example,
not type Range.

I am not really digging into "neon767's" logic; I see so many othe
errors
and questionable code.

But with respect to this one line, perhaps the following is wha
"neon767"
intends:

With wsSrc(i).Range("A1:AV" & LR)

That seems to work. Any problem with the code being ugly isn't my fault
I actually got help from someone else making it, but something I di
seemed to make it stop working. However, it seems okay now

 

[joeu2004]

I am not really digging into "neon767's" logic; I see so
many other errors and questionable code.

[....]
Any problem with the code being ugly isn't my fault [....]
However, it seems okay now.

I wrote "errors and questionable code", not "ugly". Code can __appear__ to
work by accident, when in fact it is implemented incorrectly.

But I use the word "incorrctly" advisedly, since the intention of the
implementation is not clear to me at first glance. That is, I am not taking
the time to try to intuit the code's intention; and there is important
context information that you did not mention.

For example, the statement:

LR = wsSrc(i).Range(CL & Rows.Count).End(xlUp).Row

is suspicious because Rows.Count refers to ActiveSheet, not wsSrc(i) or
wsDest, and it is unclear what ActiveSheet is or should be in the algorithm.

(Unless the subroutine is in or is executed in the context of a worksheet
module, not a normal module.)

Similarly for both Cells(...) and Rows.Count in the statement:

bottom = Cells(Rows.Count, "H").End(xlUp).Row

My guess: you have been fortunate to run this subroutine only when the
active worksheet is the "search" worksheet. (Or again, this code appears in
a worksheet module, not a normal module.)

And if that is intentional, there is no problem at all. But in that case, I
would write:

Set wsDest = ActiveSheet

(Or ``Set wsDest = Me`` in a worksheet module.)

For another example, the statements (excerpted):

wsCount = Worksheets.Count - 1
Dim wsSrc(1 To 14) As Worksheet
For i = 1 To wsCount
Set wsSrc(i) = Sheets(i)

work only if wsCount is 15 or less. I would write ``Dim wsSrc(1 To wsCount)
As Worksheet``.

But it is unclear why wsSrc is an array in the first place. Arguably,
perhaps this is an excerpt from a larger context where an array does indeed
make sense.

Finally (but not necessarily exhaustively), the statements (excerpted):

wsCount = Worksheets.Count - 1
Set wsDest = Sheets("Search")
For i = 1 To wsCount
Set wsSrc(i) = Sheets(i)

are suspicious. I suspect the purpose is to exclude "search" worksheet from
the for-loop processing. But the code works only if the "search" worksheet
is the last worksheet (tab) as they are presented in Excel.

Again, if that is understood and intentional (expected), there is no
problem.