Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Run time error Method 'Range' of Object '_Worksheet' Failed
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 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error Method 'Range' of Object '_Worksheet' Failed
neon767 wrote:
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) -- Even a coward might die bravely with nothing but a gesture. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error Method 'Range' of Object '_Worksheet' Failed
"Auric__" wrote:
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) |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error Method 'Range' of Object '_Worksheet' Failed
joeu2004 wrote:
"Auric__" wrote: 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. Totally missed that little detail. That's what I get for posting while tired. -- I intend to help you stop this madman. Whatever the cost. |
#5
|
|||
|
|||
Quote:
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error Method 'Range' of Object '_Worksheet' Failed
"neon767" wrote:
'joeu2004[_2_ Wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '1004': Method 'Range' of object '_Worksheet' failed | Excel Programming | |||
Error Help - Method "Range" of object "_Worksheet" failed. | Excel Programming | |||
Run-time error '1004': Method 'Range' of object '_Global' failed | Excel Programming | |||
Run-time error '1004': Method 'Range' of object '_Global' failed | Excel Programming | |||
HELP!!!! -- (Method Range of _Worksheet failed) ERROR! | Excel Programming |