dynamic array 1

[cazamic]

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

 

[marshg]

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!

 

[johnwm]

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:

http://www.essexsteam.co.uk