Error 9: Subscript out of range

[Conoro1894]

The following is just part of my code. i am getting an Error 9: Subscript out of range. I've tried a few things in order to correct this but nothing seems to change it.

r = 2 c = 2
i = 2
LastRowNumber = xlLastRow(Sheets("Data"))
Do While Sheets("DATA").Cells(r, 4) <> ""

Sheets("DATA").Activate

Sheets("DATA").Cells(i, 10).Name = "sheet"
Sheets("DATA").Cells(c, 11).Name = "cell"
Sheets("DATA").Cells(r, 4).Copy
Sheets(Range("sheet").Value).Activate
Range(Range("cell").Value).Select 'Activate
CenterOnCell Range((Range("cell").Value))

 

[Special-K99]

r=2 c=2

Shouldn't that be

r=2: c=2

Subscript out of range can also mean the sheet doesn't exist

Are you sure this is correct?

Sheets(Range("sheet").Value).Activate

 

[Conoro1894]

Sorry,I should have pointed out where the issue was coming from.
This is the line of code that is bringing up the error,I need to change this somehow.
Sheets(Range("sheet").Value).Activate

 

[Norie]

Is the value in the range 'sheet' the name of a worksheet in the active workbook?

 

[Conoro1894]

No it is the name of a column in the DATA worksheet. I have named this column as sheet

 

[Special-K99]

For this to work

Sheets(Range("sheet").Value).activate

Range("sheet").Value should be the name of an existing sheet not a column

 

[Conoro1894]

Ok that helps but it also means the rest of my code doesnt run either.
I have posted the relevant parts of my code belowe to see if it helps with the context of my problem.



Application.ScreenUpdating = False


Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim LastRowNumber As Integer
Dim shtCount As Integer
With Application
.ReferenceStyle = xlR1C1
End With

r = 2
c = 2
i = 2
LastRowNumber = xlLastRow(Sheets("Data"))
Do While Sheets("DATA").Cells(r, 4) <> ""

Sheets("DATA").Activate

Sheets("DATA").Cells(i, 10).Name = "sheet"
Sheets("DATA").Cells(c, 11).Name = "cell"
Sheets("DATA").Cells(r, 4).Copy




Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

 

[Conoro1894]

Ok that helps but it also means the rest of my code doesnt run either.
I have posted the relevant parts of my code belowe to see if it helps with the context of my problem.



Application.ScreenUpdating = False


Dim r As Integer
Dim c As Integer
Dim i As Integer
Dim LastRowNumber As Integer
Dim shtCount As Integer
With Application
.ReferenceStyle = xlR1C1
End With

r = 2
c = 2
i = 2
LastRowNumber = xlLastRow(Sheets("Data"))
Do While Sheets("DATA").Cells(r, 4) <> ""

Sheets("DATA").Activate

Sheets("DATA").Cells(i, 10).Name = "sheet"
Sheets("DATA").Cells(c, 11).Name = "cell"
Sheets("DATA").Cells(r, 4).Copy
Sheets(Range("sheet").Value).Activate
Range(Range("cell").Value).Select 'Activate

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

I changed a bit of code to include where my problem is occurring.

 

[Conoro1894]

Still haven't found a solution to this problem. If anyone has any new ideas,they'd be much appreciated.