CODEWITHSUNDEEP
vbaexcel
Student of the Digital World
Student of the Digital World

Reputation: 3041

Array - Subscript out of range in VBA

I am trying to store the values inside an array. I am facing a problem it says subscript out of range.

This is the code,

Sub Trial()

Dim HeaderArray() As Variant

Dim HeaderValue As String

Dim j As Long

Dim i as Long

set wk = Activeworkbook

lastrow_header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row

j = 1

      For i = 2 To lastrow_header_Config

         HeaderValue = Wk.Sheets("Config").Range("W" & i).Value

             If HeaderValue <> "" Then

              HeaderArray(j - 1) = HeaderValue // Subscript out of range error

             j = j + 1

             End If

      Next

End Sub

What is the mistake I am making. Kindly advise.

Upvotes: 1

Views: 9927

Answers (3)

Glenn G
Glenn G

Reputation: 667

try this and see how it works for you

pay close attention to the ReDim HeaderArray(j) line and the ReDim Preserve HeaderArray(j) lines

Sub Trial()
    Dim HeaderArray() As Variant
    Dim HeaderValue As String
    Dim j As Long
    Dim i As Long
        Set Wk = ActiveWorkbook
        lastrow_header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row
        j = 1
        ReDim HeaderArray(j)   '<============= initialize your array length
        For i = 2 To lastrow_header_Config
            HeaderValue = Wk.Sheets("Config").Range("W" & i).Value
            If HeaderValue <> "" Then
                ReDim Preserve HeaderArray(j) '<================= adjust your array length to accomodate the additional info
                HeaderArray(j - 1) = HeaderValue '// Subscript out of range error
                j = j + 1
            End If
        Next
End Sub

Also you might want to read up on using the option keyword. Arrays by default have the first data point at index 0 so for example array(1) creates an array that has 1 data point, however to reference that data point you would use array(0). if you wanted the first data point in the array to be referenced using array(1), then you would use the Option Base 1 keyword at the very top of your module.

Upvotes: 3

iDevlop
iDevlop

Reputation: 25252

On the first pass, j = 1. Therefore you try to set HeaderArray(0) a value, while HeaderArray is probably 1 based.
You can eventually use Option Base 0, or explicitely Redim HeaderArray(0 to 10) (or whatever value you need)

Upvotes: 1

Scott Craner
Scott Craner

Reputation: 152465

You need to declare the size of the array before trying to put data in it. Use COUNTA to find the number of cells with data in your range:

Sub Trial()

Dim HeaderArray() As Variant
Dim HeaderValue As String
Dim lastrow_Header_Config As Long
Dim j As Long
Dim i As Long

Set Wk = ActiveWorkbook

lastrow_Header_Config = Wk.Sheets("Config").Cells(Rows.Count, "W").End(xlUp).Row
ReDim HeaderArray(Application.WorksheetFunction.CountA(Wk.Sheets("Config").Range("W2:W" & lastrow_Header_Config))-1) As Variant
j = 0

For i = 2 To lastrow_Header_Config
    HeaderValue = Wk.Sheets("Config").Range("W" & i).Value
    If HeaderValue <> "" Then
        HeaderArray(j) = HeaderValue
        j = j + 1
    End If
Next

End Sub

Upvotes: 4

Related Questions