# Run time error &#39;9&#39;, subscript out of range • ### Question

• I am running the code below in Microsoft visual basic and i am getting the  error "Run time error '9' subscript out of range". Can someone help identify where the error is. When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

'This code calculates the returns,expected returns and standard deviation of return of each stock
'Number of Datapoints assumed to be 30000
'Number of shares assumed to be 300 max
Dim MeanReturns(300) As Single
Dim CentralMomnts As Double
Dim Stdev(300) As Double

Sub CalcReturns()
Application.ScreenUpdating = False
Dim Returns(300, 30000) As Single
Dim ShareNames(300) As String
Dim NumShares As Integer
Dim NumDataPnts As Integer
Worksheets("UnknownShareP").Activate
NumShares = WorksheetFunction.CountA(Range("B2:IV2"))
NumDataPnts = WorksheetFunction.CountA(Range("B2:B65536"))
Dim Sum As Double
For i = 1 To NumShares
ShareNames(i) = Cells(1, i + 1)
Sum = 0
CentralMomnts = 0
For j = 1 To NumDataPnts - 1
If WorksheetFunction.IsNumber(Cells(j + 2, i + 1)) And WorksheetFunction.IsNumber(Cells(j + 1, i + 1)) Then
Returns(i, j) = (Cells(j + 2, i + 1) - Cells(j + 1, i + 1)) / Cells(j + 1, i + 1)
Sum = Sum + Returns(i, j)
CentralMomnts = CentralMomnts + (Returns(i, j) - MeanReturns(i)) ^ 2
Else: Returns(i, j) = 0
End If
Next j
MeanReturns(i) = (Sum / (NumDataPnts - 1))
Stdev(i) = (CentralMomnts / (NumDataPnts - 2)) ^ 0.5
Next i
Worksheets("UnknownShareR").Activate
For i = 1 To NumShares
Cells(NumDataPnts + 5, i + 1) = MeanReturns(i)
Cells(NumDataPnts + 7, i + 1) = Stdev(i)
Cells(1, i + 1) = ShareNames(i)
For j = 1 To NumDataPnts - 1
Cells(j + 2, i + 1) = Returns(i, j)
Next j
Next i

End Sub

Sunday, October 9, 2016 9:25 AM

• You haven't explained what you mean by "problem" and by "solution", but the inner loop that fills UnknownShareR near the end of the code is

For j = 1 To NumDataPnts - 1
...
Next j

so this will result in one row less than the data rows on UnknownShareP.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Sunday, October 9, 2016 6:55 PM

### All replies

• I am running the code below in Microsoft visual basic and i am getting the  error "Run time error '9' subscript out of range". Can someone help identify where the error is. When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

'This code calculates the returns,expected returns and standard deviation of return of each stock
'Number of Datapoints assumed to be 30000
'Number of shares assumed to be 300 max
Dim MeanReturns(300) As Single
Dim CentralMomnts As Double
Dim Stdev(300) As Double

Sub CalcReturns()
Application.ScreenUpdating = False
Dim Returns(300, 30000) As Single
Dim ShareNames(300) As String
Dim NumShares As Integer
Dim NumDataPnts As Integer
Worksheets("UnknownShareP").Activate
NumShares = WorksheetFunction.CountA(Range("B2:IV2"))
NumDataPnts = WorksheetFunction.CountA(Range("B2:B65536"))
Dim Sum As Double
For i = 1 To NumShares
ShareNames(i) = Cells(1, i + 1)
Sum = 0
CentralMomnts = 0
For j = 1 To NumDataPnts - 1
If WorksheetFunction.IsNumber(Cells(j + 2, i + 1)) And WorksheetFunction.IsNumber(Cells(j + 1, i + 1)) Then
Returns(i, j) = (Cells(j + 2, i + 1) - Cells(j + 1, i + 1)) / Cells(j + 1, i + 1)
Sum = Sum + Returns(i, j)
CentralMomnts = CentralMomnts + (Returns(i, j) - MeanReturns(i)) ^ 2
Else: Returns(i, j) = 0
End If
Next j
MeanReturns(i) = (Sum / (NumDataPnts - 1))
Stdev(i) = (CentralMomnts / (NumDataPnts - 2)) ^ 0.5
Next i
Worksheets("UnknownShareR").Activate
For i = 1 To NumShares
Cells(NumDataPnts + 5, i + 1) = MeanReturns(i)
Cells(NumDataPnts + 7, i + 1) = Stdev(i)
Cells(1, i + 1) = ShareNames(i)
For j = 1 To NumDataPnts - 1
Cells(j + 2, i + 1) = Returns(i, j)
Next j
Next i

End Sub

Sunday, October 9, 2016 8:02 AM
• When i debug, its highlighting this area " Worksheets("UnknownShareP").Activate"

That suggests that the named worksheet does not exist.

Questions relating to VBA are best asked at Visual Basic for Applications  (VBA)

Sunday, October 9, 2016 9:12 AM
• The code runs on activeworkbook. Check the sheet "UnknownShareP" is visible in workbook from where you are running the code.

Best Regards,

Sunday, October 9, 2016 9:52 AM
• Yes it is visible,  and when debugging its highlighting " Worksheets("UnknownShareP").Activate"
Sunday, October 9, 2016 10:32 AM
• Make sure that the name UnknownShareP is exactly correct. Perhaps there is a space before or after the name, so that it's actually " UnknownShareP" or "UnknownShareP ".

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Sunday, October 9, 2016 10:43 AM
• Thank you, i finally managed to run the code.

However, problem has 21 rows, where as after running the code its only giving 19 rows. I checked a more or less problem that was done, the solution has 22 rows. How can  i fix this?

Sunday, October 9, 2016 4:47 PM
• You haven't explained what you mean by "problem" and by "solution", but the inner loop that fills UnknownShareR near the end of the code is

For j = 1 To NumDataPnts - 1
...
Next j

so this will result in one row less than the data rows on UnknownShareP.

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Sunday, October 9, 2016 6:55 PM
• Hi patrickmupam,

Have your original issue related with “UnknownShareP” been resolved? If so, I suggest you try below code to get the current active workbook, and the worksheets exist in this workbook.

```Dim s As Worksheet
For Each s In Worksheets
Debug.Print s.Name, ActiveWorkbook.Name
Next s```

For wrong rows, I agree with Hans. If your issue has been resolved, I would suggest you mark the helpful reply as answer, and then others who run into the same issue would find the solution easily.

Best Regards,

Edward

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.