Code Snippets


Option Explicit 

Dim gvaPositionsPrevious As Variant
Dim gvaPositionsCurrent As Variant

Public Enum enARRAY_ENUM
    eKey
    eName
    eDescription
    ePrevious
    eCurrent
End Enum

Public Sub Arrays_Processing()

Dim lcount_previous As Long
Dim lcount_current As Long
Dim blast_previous As Boolean
Dim blast_current As Boolean
Dim lrow_output As Long
Dim boutput_previous As Boolean
Dim boutput_current As Boolean
Dim boutput_both As Boolean

    On Error GoTo ErrorHandler

    gvaPositionsPrevious = Range("PositionsPrevious").Value
    gvaPositionsCurrent = Range("PositionsCurrent").Value
    lrow_output = 3
    lcount_previous = 1
    lcount_current = 1
    Sheets("Previous vs Current").Select
    Range("B3:I5000").ClearContents
    With Sheets("Previous vs Current")

        Do Until ((blast_previous = True) And (blast_current = True))

            boutput_both = False
            boutput_current = False
            boutput_previous = False
            If (blast_previous = True) Then boutput_current = True
            If (blast_current = True) Then boutput_previous = True
            If ((blast_previous = False) And _
                (blast_current = False)) Then
                If (gvaPositionsPrevious(lcount_previous, enARRAY_ENUM.eKey) = _
                    gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eKey)) Then
                    boutput_both = True
                Else
                    If (gvaPositionsPrevious(lcount_previous, enARRAY_ENUM.eKey) < _
                        gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eKey)) Then
                        boutput_previous = True
                    Else
'therefore Previous > Current, so display Current
                        boutput_current = True
                    End If
                End If
            End If

            If (boutput_both = True) Then
                .Range("B" & lrow_output).Value = _
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eName)
                .Range("C" & lrow_output).Value = _
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eDescription)
'display previous
                .Range("D" & lrow_output).Value = _
                      gvaPositionsPrevious(lcount_current, enARRAY_ENUM.ePrevious)
'display current
                .Range("E" & lrow_output).Value = _
                      gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eCurrent)
                If (lcount_previous = UBound(gvaPositionsPrevious, 1)) Then
                   blast_previous = True
                End If
                If (lcount_previous < UBound(gvaPositionsPrevious, 1)) Then
                   lcount_previous = lcount_previous + 1
                End If
                If (lcount_current = UBound(gvaPositionsCurrent, 1)) Then
                   blast_current = True
                End If
                If (lcount_current < UBound(gvaPositionsCurrent, 1)) Then
                   lcount_current = lcount_current + 1
                End If
            End If

            If (boutput_previous = True) Then
                .Range("B" & lrow_output).Value = _
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.eName)
                .Range("C" & lrow_output).Value = _
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.eDescription)
'display previous
                .Range("D" & lrow_output).Value = _
                    gvaPositionsPrevious(lcount_current, enARRAY_ENUM.ePrevious)
                If (lcount_previous = UBound(gvaPositionsPrevious, 1)) Then
                    blast_previous = True
                End If
                If (lcount_previous < UBound(gvaPositionsPrevious, 1)) Then
                    lcount_previous = lcount_previous + 1
                End If
            End If

            If (boutput_current = True) Then
                .Range("B" & lrow_output).Value = _
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eName)
                .Range("C" & lrow_output).Value = _
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eDescription)
'display current
                .Range("E" & lrow_output).Value = _
                   gvaPositionsCurrent(lcount_current, enARRAY_ENUM.eCurrent)
                If (lcount_current = UBound(gvaPositionsCurrent, 1)) Then
                   blast_current = True
                End If
                If (lcount_current < UBound(gvaPositionsCurrent, 1)) Then
                   lcount_current = lcount_current + 1
                End If
            End If

'Difference between current and previous
            .Range("F" & lrow_output).FormulaR1C1 = "RC[-1]-RC[-2]"
            lrow_output = lrow_output + 1
        Loop
    End With
    Range("B3").Sort Key1:=Range("C3"), Order1:=xlAscending, _
                     Key2:=Range("C4"), Order2:=xlAscending, Header:=xlYes
    Exit Sub

ErrorHandler:
End Sub

Public Sub MatchingArrays() 
'Dim vArray1 As Variant
'Dim vArray2 As Variant
Dim larray1_count As Long
Dim larray2_count As Long
Dim barray1_last As Boolean
Dim barray2_last As Boolean

Dim lcount_combined As Long

ReDim vCombinedList(1 To UBound(vArray1, 1) + UBound(vArray2, 1))

larray1_count = 1
larray2_count = 1
lcount_combined = 1

Do Until (barray1_last = True) And (barray2_last = True)

If vArray1(larray1_count) = vArray2(larray2_count) Then

vCombinedList(lcount_combined) = vArray1(larray1_count)
lcount_combined = lcount_combined + 1


If larray1_count < UBound(vArray1, 1) Then larray1_count = larray1_count + 1
If larray2_count < UBound(vArray2, 1) Then larray2_count = larray2_count + 1

If larray1_count = UBound(vArray1, 1) Then barray1_last = True
If larray2_count = UBound(vArray2, 1) Then barray2_last = True
Else
If vArray1(larray1_count) < vArray2(larray2_count) Then

If larray1_count = UBound(vArray1, 1) Then barray1_last = True
If larray1_count < UBound(vArray1, 1) Then larray1_count = larray1_count + 1
Else

If larray2_count = UBound(vArray2, 1) Then barray2_last = True
If larray2_count < UBound(vArray2, 1) Then larray2_count = larray2_count + 1
End If
End If
Loop

ReDim Preserve vCombinedList(1 To lcount_combined - 1)

Stop

End Sub


© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrev