×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

dynamic array

dynamic array

dynamic array

(OP)
Hi can someone help please. I have an array list which merges and and creates another list.  It works fine but I need to change it to become a dynamic array and I am stuck.  Can anyone help please.
The code is below.

Sub MergeLists()
    Dim List1(93) As String, List2(102) As String
    Dim List3() As String
    Dim Index1 As Integer, Index2 As Integer, Index3 As Integer
    Dim Name1 As String, Name2 As String
   ' Dim NI As Integer
'For loop variable
    Dim i As Integer
    
       ' With Range("A4")
    
      '      NI = Range(.Offset(1, 0), .End(xlDown)).Rows.Count
       ' End With
        
     'Copy Customer name into array list 1 and list 2
        For i = 1 To 93
        List1(i) = Range("A5").Cells(i)
        Next i
    
        For i = 1 To 102
            List2(i) = Range("B5").Cells(i)
        Next i
        
    'Initialise index for ist 1 and List 2
        Index1 = 1
        Index2 = 1
    
    'Implete merge algorit
        Do While (Index1 <= 93) And (Index2 <= 102)
            Name1 = List1(Index1)
            Name2 = List2(Index2)
        
            Index3 = Index3 + 1
        ReDim Preserve List3(Index3)
        
        If Name1 < Name2 Then
            List3(Index3) = Name1
            Index1 = Index1 + 1
        ElseIf Name1 > Name2 Then
            List3(Index3) = Name2
            Index2 = Index2 + 1
        Else
            List3(Index3) = Name1
            Index1 = Index1 + 1
            Index2 = Index2 + 1
        End If
    Loop
    
    'copy remaining names lseft in List 1 to List 3
        
        For i = Index1 To 93
            Index3 = Index3 + 1
            ReDim Preserve List3(Index3)
            List3(Index3) = List1(i)
        Next i
        
    'copy any remaining names left in list 2 to list 3

        For i = Index2 To 102
            Index3 = Index3 + 1
            ReDim Preserve List3(Index3)
            List3(Index3) = List2(i)
            
            Next i
            
    ' Using For loop copy each name into merged list 3 to column D of worksheet
           
         With Range("D4")
            For i = 1 To Index3
                .Offset(i, 0).Value = List3(i)
                Next i
            End With

    'Place curser in cell A2

        Range("A2").Select
               
            
            
End Sub

RE: dynamic array

When you need to change the size of the array, try using Redim.  i.e.  Redim List1(100)   Just a thought

I've seen this in a VB book, but have never used it.  Good Luck!

RE: dynamic array

For a dynamic array do your first declaration thus:

Dim list1() as String

When you want to first use it, set the size:

Redim List1(10)

When you want to change size and lose the contents

Redim List1(20)

If you want to increase size and retain the contents

Redim Preserve List1(30)

Good Luck
johnwm
________________________________________________________
To get the best from these forums read FAQ731-376 before posting

UK steam enthusiasts: www.essexsteam.co.uk

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources