Flora Edwards
asked on
Can anyone please comment this VBA code what each line/part of the code does?
someone helped me with the code below.
it uses three different classes CreateObject("System.Colle ctions.Arr ayList") & With CreateObject("System.Colle ctions.Sor tedList") and = CreateObject("Scripting.Di ctionary")
i have seen previously Scripting.Dictionary" but i have never seen the other two system.collections.arrayli st and sortedlist.
i googled, only in microsoft support page there is a very little detail and no example. where and how it is used.
i need help with commeting the code below, that what line of the code does what.
also i need help if someone could lead me to somewhere where i can learn more about the two classes system.collections.arrayli st and system.collections.sortedl ist. with example
it uses three different classes CreateObject("System.Colle
i have seen previously Scripting.Dictionary" but i have never seen the other two system.collections.arrayli
i googled, only in microsoft support page there is a very little detail and no example. where and how it is used.
i need help with commeting the code below, that what line of the code does what.
also i need help if someone could lead me to somewhere where i can learn more about the two classes system.collections.arrayli
Option Explicit
Sub Macro()
Dim a, i As Long, ii As Long, AL As Object
Set AL = CreateObject("System.Collections.ArrayList")
a = Sheets("data").Cells(1).CurrentRegion.Value
With CreateObject("System.Collections.SortedList")
For i = 2 To UBound(a, 1)
If (a(i, 2) <> 111) * (Not AL.Contains(a(i, 2))) Then AL.Add a(i, 2)
If a(i, 3) Like "[5-7]*" Then
If Not .Contains(a(i, 1)) Then
Set .Item(a(i, 1)) = CreateObject("Scripting.Dictionary")
End If
.Item(a(i, 1))(a(i, 2)) = .Item(a(i, 1))(a(i, 2)) + a(i, 4)
End If
Next
ReDim a(1 To AL.Count + 1, 1 To .Count + 1): AL.Sort
a(1, 1) = "Months/Years"
For i = 0 To AL.Count - 1
a(i + 2, 1) = AL(i)
Next
For ii = 0 To .Count - 1
a(1, ii + 2) = .GetKey(ii)
For i = 2 To UBound(a, 1)
a(i, ii + 2) = .GetByIndex(ii)(a(i, 1))
Next
Next
End With
With Sheets("main").Cells(1).Resize(UBound(a, 1), UBound(a, 2))
.ClearContents
.Value = a: .Columns.AutoFit
End With
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER