get2noesks
New Member
- Joined
- Jul 15, 2015
- Messages
- 24
The code is failing on the red marked line. Can someone please help me with it.
<code>
Private Sub Workbook_Open()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Dim testCollection As Collection
Dim SIZE As Integer, sortCol As Integer, sortRow As Integer, loopcntl As Integer
Set testCollection = getValuesToSort
'Debug.Print testCollection.Item(3)
'Debug.Print testCollection.Count
sortRow = 1
loopcntl = 1
SIZE = testCollection.Count
If SIZE <= 0 Then
MsgBox ("Nothing To Search for a sort")
End If
For loopcntl = 1 To SIZE
'MsgBox (loopCntl)
'MsgBox testCollection.Item(loopCntl)
'Call sortData(sortRow, loopCntl)
Dim completeArray() As String, arrayLeft() As String, arrayRight() As String
Dim arrayInputCntlVar As Integer, firstIndex As Integer
firstIndex = sortRow
arrayInputCntlVar = testCollection.Item(loopcntl)
Dim i As Integer, arrSize As Integer
i = 0
arrSize = WorksheetFunction.CountA(WS1.Columns(arrayInputCntlVar))
MsgBox (arrSize)
ReDim completeArray(arrSize)
While (WS1.Cells(sortRow + 1, arrayInputCntlVar)) <> ""
completeArray(i) = WS1.Cells(sortRow + 1, arrayInputCntlVar).Value
sortRow = sortRow + 1
i = i + 1
Wend
Dim lastIndex As Integer
lastIndex = i + 1
Call MergeSort(completeArray(), firstIndex, lastIndex)
Next loopcntl
End Sub
Public Sub MergeSort(list() As String, ByVal first_index As Integer, ByVal last_index As Integer)
Dim middle As Integer
If (last_index > first_index) Then
' Recursively sort the two halves of the list.
middle = (first_index + last_index) / 2
Call MergeSort(list, first_index, middle)
Call MergeSort(list, middle + 1, last_index)
' Merge the results.
Call Merge(list, first_index, middle, last_index)
End If
End Sub
Public Sub Merge(list() As String, ByVal beginning As Integer, ByVal middle As Integer, ByVal ending As Integer)
Dim temp_array() As String
Dim temp As Integer
Dim counterA As Integer
Dim counterB As Integer
Dim counterMain As Integer
' Copy the array into a temporary array.
ReDim temp_array(beginning To ending)
CopyMemory temp_array(beginning), list(beginning), (ending - beginning + 1) * Len(list(beginning))
' counterA and counterB mark the next item to save
' in the first and second halves of the list.
counterA = beginning
counterB = middle + 1
' counterMain is the index where we will put the
' next item in the merged list.
counterMain = beginning
Do While (counterA <= middle) And (counterB <= ending)
' Find the smaller of the two items at the front
' of the two sublists.
If (temp_array(counterA) <= temp_array(counterB)) _
Then
' The smaller value is in the first half.
list(counterMain) = temp_array(counterA)
counterA = counterA + 1
Else
' The smaller value is in the second half.
list(counterMain) = temp_array(counterB)
counterB = counterB + 1
End If
counterMain = counterMain + 1
Loop
' Copy any remaining items from the first half.
If counterA <= middle Then
CopyMemory list(counterMain), temp_array(counterA), (middle - counterA + 1) * Len(list(beginning))
End If
' Copy any remaining items from the second half.
If counterB <= ending Then
CopyMemory list(counterMain), temp_array(counterB), (ending - counterB + 1) * Len(list(beginning))
End If
End Sub
</code>
Regards
Saurabh
<code>
Private Sub Workbook_Open()
Dim WS1 As Worksheet
Dim WS2 As Worksheet
Set WS1 = Worksheets("Sheet1")
Set WS2 = Worksheets("Sheet2")
Dim testCollection As Collection
Dim SIZE As Integer, sortCol As Integer, sortRow As Integer, loopcntl As Integer
Set testCollection = getValuesToSort
'Debug.Print testCollection.Item(3)
'Debug.Print testCollection.Count
sortRow = 1
loopcntl = 1
SIZE = testCollection.Count
If SIZE <= 0 Then
MsgBox ("Nothing To Search for a sort")
End If
For loopcntl = 1 To SIZE
'MsgBox (loopCntl)
'MsgBox testCollection.Item(loopCntl)
'Call sortData(sortRow, loopCntl)
Dim completeArray() As String, arrayLeft() As String, arrayRight() As String
Dim arrayInputCntlVar As Integer, firstIndex As Integer
firstIndex = sortRow
arrayInputCntlVar = testCollection.Item(loopcntl)
Dim i As Integer, arrSize As Integer
i = 0
arrSize = WorksheetFunction.CountA(WS1.Columns(arrayInputCntlVar))
MsgBox (arrSize)
ReDim completeArray(arrSize)
While (WS1.Cells(sortRow + 1, arrayInputCntlVar)) <> ""
completeArray(i) = WS1.Cells(sortRow + 1, arrayInputCntlVar).Value
sortRow = sortRow + 1
i = i + 1
Wend
Dim lastIndex As Integer
lastIndex = i + 1
Call MergeSort(completeArray(), firstIndex, lastIndex)
Next loopcntl
End Sub
Public Sub MergeSort(list() As String, ByVal first_index As Integer, ByVal last_index As Integer)
Dim middle As Integer
If (last_index > first_index) Then
' Recursively sort the two halves of the list.
middle = (first_index + last_index) / 2
Call MergeSort(list, first_index, middle)
Call MergeSort(list, middle + 1, last_index)
' Merge the results.
Call Merge(list, first_index, middle, last_index)
End If
End Sub
Public Sub Merge(list() As String, ByVal beginning As Integer, ByVal middle As Integer, ByVal ending As Integer)
Dim temp_array() As String
Dim temp As Integer
Dim counterA As Integer
Dim counterB As Integer
Dim counterMain As Integer
' Copy the array into a temporary array.
ReDim temp_array(beginning To ending)
CopyMemory temp_array(beginning), list(beginning), (ending - beginning + 1) * Len(list(beginning))
' counterA and counterB mark the next item to save
' in the first and second halves of the list.
counterA = beginning
counterB = middle + 1
' counterMain is the index where we will put the
' next item in the merged list.
counterMain = beginning
Do While (counterA <= middle) And (counterB <= ending)
' Find the smaller of the two items at the front
' of the two sublists.
If (temp_array(counterA) <= temp_array(counterB)) _
Then
' The smaller value is in the first half.
list(counterMain) = temp_array(counterA)
counterA = counterA + 1
Else
' The smaller value is in the second half.
list(counterMain) = temp_array(counterB)
counterB = counterB + 1
End If
counterMain = counterMain + 1
Loop
' Copy any remaining items from the first half.
If counterA <= middle Then
CopyMemory list(counterMain), temp_array(counterA), (middle - counterA + 1) * Len(list(beginning))
End If
' Copy any remaining items from the second half.
If counterB <= ending Then
CopyMemory list(counterMain), temp_array(counterB), (ending - counterB + 1) * Len(list(beginning))
End If
End Sub
</code>
Regards
Saurabh