CODEWITHSUNDEEP
vbaexcel
Jay
Jay

Reputation: 47

Subscript Out of Range, even though value is defined

I am writing a piece of code that transfers selected data on an Excel sheet into an array, which is then used to print the data on a new spreadsheet. However, I am getting a "Subscript Out of Range" error, even though a value appears when I scroll over selectArr(i - 1). Here is my code:

Sub Marascuilo()
    Dim numRows As Integer 'Number of rows selected
    numRows = Selection.Rows.Count
    Dim selectArr() As Double 'Array containing numbers from selected cells
    selectArr = loadArr(numRows) 'Load values into array
    For i = 2 To UBound(selectArr) - LBound(selectArr) + 2
        Sheets("Sheet 4").Cells(i, 2).Value = selectArr(i - 1)
    Next
End Sub

'This function loads the values from the selected cells into selectArr.
Function loadArr(numRows) As Double()
    Dim ResultArray() As Double
    r = 1
    For Each v In Selection
        ReDim Preserve ResultArray(1 To r)
        If v <> "" Then
            ResultArray(r) = v.Value
            r = r + 1
        End If
    Next
    loadArr = ResultArray
End Function

Any ideas as to how I fix this issue?

Thanks!

Jay

Upvotes: 2

Views: 2696

Answers (1)

Dick Kusleika
Dick Kusleika

Reputation: 33165

Instead of using Sheets("Sheet 4"), you might consider using the sheet's CodeName. If you look in the Project Explorer window, every sheet has a Name and a CodeName. It might look like this

Sheet1 (Sheet1)
Sheet2 (Sheet2)

The first one is the CodeName (can't be changed from the UI). The one in parens is the tab name. Select the sheet in the Project Explorer and press F4 to open the Properties dialog. Go to the (Name) property (a poorly named property) and change it to something meaningful. I change all my sheets' CodeNames and use a wsh prefix. My sheet that's a log has a CodeName of

wshLog

Now I can use wshLog in my code and I get some benefits. The first is that if someone renames the sheet in the UI, the code still works. The second is I can type wshlog (all lower case) and the VBE will change it to wshLog and I get that visual cue that I spelled it right. Finally, my code is more readable, ex wshFinalReport vs. Sheets("Sheet1").

Upvotes: 1

Related Questions