how to assign values to arrays using loop

Question

I want to assign values to arrays from a sheet using loop

I tried using this but gives error "Subscript out of Range"

 i=1
 With ws
        Do While i <= 40
            ReDim Preserve WorkID(1 To i)
            ReDim Preserve Work(1 To i)
            ReDim Preserve ComposerName(1 To i)

            WorkID(i) = Range("A" & i + 1).Value
            Work(i) = Range("B" & i + 1).Value
            ComposerName(i) = Range("C" & i + 1).Value
        i = i + 1
        Loop
    End With

I tried both ways to initialize but none of them worked

Initialize Type 1

Dim WorkID() As Variant              
Dim Work() As Variant                
Dim ComposerName() As Variant

Initialize Type 2

Dim WorkID(1 to 40) As Variant              
Dim Work(1 to 40) As Variant                
Dim ComposerName(1 to 40) As Variant

Also I tried without Redim as well like this but nothing worked:

   i=1
   With ws
        Do While i <= 40
            WorkID(i) = Range("A" & i + 1).Value
            Work(i) = Range("B" & i + 1).Value
            ComposerName(i) = Range("C" & i + 1).Value
        i = i + 1
        Loop
    End With

Full Sub here :

Option Explicit
Sub Join()

Dim WorkID()             'Stores the workID from Works Sheet
Dim Work()                 'Stores the work from Works Sheet
Dim ComposerName()       'Stores the composer from Works Sheet
Dim ConductorID()          'Stores the ConductorID from Conductors Sheet
Dim ConductorNames()       'Stores Conductor Names from Conductors Sheet
Dim CDWorkID()           'Stores CDWorkID  from CD Sheet
Dim CDCondID()                 'Stores CDConductor ID  from CD Sheet

Dim i, j, k, m As Long
Dim ws, wcon, wcd, wj As Worksheet

Set ws = Sheets("Works")
Set wcon = Sheets("Conductors")
Set wcd = Sheets("CDs")
Set wj = Sheets("Join")

i = j = k = 1                                   'Initalize

ws.Activate

        Do While i <= 40
            ReDim Preserve WorkID(1 To i)
            ReDim Preserve Work(1 To i)
            ReDim Preserve ComposerName(1 To i)

            WorkID(i) = Range("A" & i + 1).Value
            Work(i) = Range("B" & i + 1).Value
            ComposerName(i) = Range("C" & i + 1).Value
        i = i + 1
        Loop

wcon.Activate
        Do While j <= 10
            ReDim Preserve ConductorID(1 To j)
            ReDim Preserve ConductorNames(1 To j)
            ConductorID(j) = Range("A" & j + 1).Value
            ConductorNames(j) = Range("B" & j + 1).Value
            j = j + 1
        Loop

wcd.Activate

        Do While k <= 132
            ReDim Preserve CDWorkID(1 To k)
            ReDim Preserve CDCondID(1 To k)
            CDWorkID(k) = Range("A" & k + 1).Value
            CDCondID(k) = Range("B" * k + 1).Value
        k = k + 1
        Loop

wj.Activate    
        For i = LBound(CDWorkID) To UBound(CDWorkID)
        Range("F" & i) = CDWorkID(i)
        Next i

End Sub

What is the point in trying to redim in a loop rather than redim once before the loop is entered? — John Coleman
– John Coleman, Commented Oct 24, 2015 at 15:27

John Coleman · Accepted Answer · 2015-10-24 19:10:16Z

3

RedDim Preserve is generally an expensive operation since it involves allocating space for a larger array and moving contents from the old array. It is almost always a bad idea to use it inside of a loop. Instead -- determine ahead of time how big the arrays need to be and ReDim just once. If you don't know ahead of time, make them larger than needed and then use a ReDim Preserve after the loop to trim them down to size. In your case, I would Redim the arrays before entering for loops (or even -- why not Dim them the right size to begin with?). Also -- prefix each range with the appropriate worksheet variable rather than activating each in turn. Something like:

Sub Join()

Dim WorkID()             'Stores the workID from Works Sheet
Dim Work()                 'Stores the work from Works Sheet
Dim ComposerName()       'Stores the composer from Works Sheet
Dim ConductorID()          'Stores the ConductorID from Conductors Sheet
Dim ConductorNames()       'Stores Conductor Names from Conductors Sheet
Dim CDWorkID()           'Stores CDWorkID  from CD Sheet
Dim CDCondID()                 'Stores CDConductor ID  from CD Sheet

Dim i As Long
Dim ws, wcon, wcd, wj As Worksheet

Set ws = Sheets("Works")
Set wcon = Sheets("Conductors")
Set wcd = Sheets("CDs")
Set wj = Sheets("Join")

ReDim WorkID(1 To 40)
ReDim Work(1 To 40)
ReDim ComposerName(1 To 40)
For i = 1 To 40
    WorkID(i) = ws.Range("A" & i + 1).Value
    Work(i) = ws.Range("B" & i + 1).Value
    ComposerName(i) = ws.Range("C" & i + 1).Value
Next i

ReDim ConductorID(1 To 10)
ReDim ConductorNames(1 To 10)
For i = 1 To 10
    ConductorID(i) = wcon.Range("A" & i + 1).Value
    ConductorNames(i) = wcon.Range("B" & i + 1).Value
Next i

ReDim CDWorkID(1 To 132)
ReDim CDCondID(1 To 132)
For i = 1 To 132
    CDWorkID(k) = wcd.Range("A" & i + 1).Value
    CDCondID(k) = wcd.Range("B" & i + 1).Value
Next i

For i = LBound(CDWorkID) To UBound(CDWorkID)
    wj.Range("F" & i) = CDWorkID(i)
Next i

End Sub

edited Oct 24, 2015 at 19:10

answered Oct 24, 2015 at 16:02

John Coleman

52.1k7 gold badges59 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

John Coleman Over a year ago

@user1016274 My solution addressed the subscript out of range error that OP mentioned, since they were redimensioning to 1 to i but then trying to assign to index i+1. You are correct that OP had a typo which would trigger another error and survived into my edit of their code. I will correct it here and upvote your contribution. Thanks.

user1016274 Over a year ago

Sorry, there is not one assignment into somearray(i+1) in the code (OP's and yours). Indexing into a range is not bound, at least not here. This is not leading to any error. Could you please clarify?

John Coleman Over a year ago

@user1016274 I think I misread it at first. Now it seems mysterious where the subscript out of range error comes from since the typo you noticed doesn't trigger that particular error.

user1016274 Over a year ago

Thank you for pushing me to look harder, I've edited my answer. As the code runs OK after eliminating this type only the OP can clarify if this solves it or not.

user1016274 · Accepted Answer · 2015-10-24 20:40:05Z

2

Range("B" * k + 1).Value has a typo - you meant Range("B" & k + 1).Value. This makes the range raise an "type" error. Eliminating this makes the code run without error - I suspect the error message is incorrect.

BTW, there is another pitfall (which does not lead to a runtime error, at least not for the code shown):
Dim i, j, k, m As Long Dim ws, wcon, wcd, wj As Worksheet
will NOT declare i, j, kas Integer but as Variants. Same for ws, wcon, wcd which are variants and NOT worksheet objects.

edited Oct 24, 2015 at 20:40

answered Oct 24, 2015 at 18:50

user1016274

4,2792 gold badges25 silver badges20 bronze badges

Collectives™ on Stack Overflow

how to assign values to arrays using loop

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related