Worksheets: Run-time Error 9

thampw · Jan 28th 2005

Hi,

I'm a bit lost at why I'm getting Run-time Error 9 (Subscript out of range) for the line Set ws2...

Code

Sub FillData()
Dim sheet_name(1 To 6) As String, ws1 As Worksheet, ws2 As Worksheet


Set ws1 = ThisWorkbook.Worksheets("Loads")


sheet_name(1) = "HiWin04"
sheet_name(2) = "LowWin04"
sheet_name(3) = "HiSum0304"
sheet_name(4) = "LowSum0304"
sheet_name(5) = "HiMid04"
sheet_name(6) = "LowMid04"


For i = 1 To 6


Set ws2 = ThisWorkbook.Worksheets(sheet_name(i)) 'This line gives run-time error


numrows = ws1.Range("A65536").End(xlUp).Row


Next i
End Sub

Display More

Thanks.

**iwrk4dedpr** · Jan 28th 2005

Re: Worksheets: Run-time Error 9

thampw,

I've gotten this error myself. Not sure but the Worksheets("") argument needs to be a string. Now I see that the variable is declared as a string but try putting it inside the cstr() function.

p45cal · Jan 28th 2005

Re: Worksheets: Run-time Error 9

Most likely because when execution gets to that line there isn't a sheet named "HiWin04"

Are you renaming sheets here?
try
ThisWorkbook.Worksheets(i).Name = sheet_name(i)
in the loop
Pascal

tinyjack · Jan 28th 2005

Re: Worksheets: Run-time Error 9

I would make sure you have no spaces in your worksheet names that you have missed.

TJ

thampw · Jan 29th 2005

Re: Worksheets: Run-time Error 9

Thanks for the replies. It was odd because I checked the worksheet and the spelling looks correct. However, out of curiousity, I retyped the worksheet name and it worked after that! Don't ask me why though.

**norie** · Jan 29th 2005

Re: Worksheets: Run-time Error 9

Could have been a trailing/leading space, which can sometimes be hard to spot.

**iwrk4dedpr** · Jan 29th 2005

Re: Worksheets: Run-time Error 9

thampw,

After a few times of programming you'll begin to use the trim function on strings because of those leading / trailing spaces. I know alot of my issues can come from them.

Worksheets: Run-time Error 9

Participate now!

Share