Method 'Range' of object'_Worksheet' failed

Philosophaie · Sep 18th 2015

This code is being run on "Sheet1".

Code

Set rng1 = Range(Sheets("Sheet2").Cells(4, 1), Sheets("Sheet2").Cells(28, 1))

Error:

Quote

"Method 'Range' of object'_Worksheet' failed"

jindon · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

1

Code

Set rng1 = Sheets("sheet2").Range(Sheets("Sheet2").Cells(4, 1), Sheets("Sheet2").Cells(28, 1))

2

Code

With Sheets("sheet2")
        Set rng1 = .Range(.Cells(4, 1), .Cells(28, 1))
    End With

**S O** · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

If you use "Range()" without qualifying it with a worksheet object it will refer to the ActiveSheet. So you're existing code is saying "I want to use a range on this sheet, that is made up of cells on another sheet" which obviously doesn't make sense and so the code fails.

You'll notice that jindon's code uses the sheet object to qualify the ranges which is why it works.

Philosophaie · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

Still get an error. Sheets "Sheet1" and "Sheet2" exist.

Code

Set rng1 = Sheets("Sheet1").Range(Sheets("Sheet2").Cells(4, 1), Sheets("Sheet2).Cells(28, 1))

Error:
"Application-defined or object-defined error"

**S O** · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

Quote

Set rng1 = Sheets("Sheet1").Range(Sheets("Sheet2").Cells(4, 1), Sheets("Sheet2).Cells(28, 1))

You're still trying to create a range on sheet 1, with cells from sheet 2 - that doesn't make sense. What are you actually trying to do? Adding some context may help here.

Philosophaie · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

Here is the whole thing. the chart objects: "XValue" and "Value" are where the errors are.

Code

nn = 2
        Dim chtObj As ChartObject
        Set chtObj = Sheets("Graph").ChartObjects("Chart 1")
        With chtObj.Chart
            For k = 1 To 9
                .SeriesCollection(k).XValues = Sheets("PlotData").Range(Sheets("PlotData").Cells(4, nn), Sheets("PlotData").Cells(28, nn))
                nn = nn + 1
                Set rng2 = 
                .SeriesCollection(k).Values = Sheets("PlotData").Range(Sheets("PlotData").Cells(4, nn), Sheets("PlotData").Cells(28, nn))
                nn = nn + 1
                .SeriesCollection(k).Interior.Color = RGB(255, 0, 0)
                .SeriesCollection(k).Name = Names(k)
                
            Next k
        End With

Display More

**S O** · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

Without being able to run the code it's hard to try and debug it. Can you upload an example workbook with the code that's not working and your charts etc.

[sw]*[/sw]

Philosophaie · Sep 18th 2015

Re: Method 'Range' of object'_Worksheet' failed

forum.ozgrid.com/index.php?attachment/66985/

I know there is a simpler way to just do a simple range.

I eventually want to loop the plot to include more columns.

Here is an example of how a Range in another sheet is not working. See above.

Method 'Range' of object'_Worksheet' failed

Participate now!

Share

Users Viewing This Thread