Sub LoadListbox()
Dim sh1 As Worksheet, sh As Worksheet
Dim a As Variant, b() As Variant, c As Variant, d As Variant, e As Variant
Dim dic As Object
Dim arSh As Variant, itSh As Variant
Dim i As Long, j As Long, k As Long, m As Long, n As Long
Dim p As Long, q As Long, u As Long
Dim x1 As Double, x2 As Double, x3 As Double, y1 As Double, y2 As Double, y3 As Double
Set dic = CreateObject("Scripting.Dictionary")
'first sheet
a = Sheets("STA").Range("A2", Sheets("STA").Range("H" & Rows.Count).End(3)).Value
'second sheet
d = Sheets("RPA").Range("A1", Sheets("RPA").Range("H" & Rows.Count).End(3)).Value
'Sheet names, from 2 to last
arSh = Array("RPA", "SR", "RR", "SS")
'
u = UBound(arSh) + 2
ReDim c(1 To UBound(a, 1) + UBound(d, 1), 1 To 9 + u)
ListBox1.ColumnCount = 9 + u
m = 6 'Initial column inside the listbox for the sheets
'
'For the first sheet
For i = 1 To UBound(a)
dic(a(i, 2)) = i
For j = 1 To 6 'UBound(a, 2)
c(i, j) = a(i, j)
Next
c(i, m + u) = a(i, 6) 'qty
c(i, m + u + 1) = "1|" & a(i, 7) 'unit cost
c(i, m + u + 2) = "1|" & a(i, 8) 'unit sale
'c(i, m + u + 3) = (c(i, m + u + 2) - c(i, m + u + 1)) * c(i, m + u)
Next i
'
'For the second sheet
p = dic.Count 'Number of indices
For i = 2 To UBound(d)
If Not dic.exists(d(i, 2)) Then
p = p + 1
dic(d(i, 2)) = p
For j = 1 To 5
c(p, j) = d(i, j)
Next j
If d(1, 7) = WorksheetFunction.Trim("UNIT COST") Then
c(p, m + u + 1) = "1|" & d(i, 7)
ElseIf WorksheetFunction.Trim("UNIT SALE") Then
c(p, m + u + 2) = "1|" & d(i, 7)
End If
End If
Next i
'
n = 7 'To increase the column for each sheet
q = 1 'If it's odd or even
For itSh = 0 To UBound(arSh)
Set sh = Sheets(arSh(itSh))
q = q + 1
Erase b()
b = sh.Range("A1", sh.Range("H" & Rows.Count).End(3)).Value
For i = 2 To UBound(b)
If dic.exists(b(i, 2)) Then
k = dic(b(i, 2))
c(k, n) = c(k, n) + b(i, 6)
If q Mod (2) = 0 Then
c(k, m + u) = c(k, m + u) + b(i, 6)
Else
c(k, m + u) = c(k, m + u) - b(i, 6)
End If
x1 = Split(c(k, m + u + 1), "|")(0)
x2 = Split(c(k, m + u + 1), "|")(1)
y1 = Split(c(k, m + u + 2), "|")(0)
y2 = Split(c(k, m + u + 2), "|")(1)
If b(1, 7) = WorksheetFunction.Trim("UNIT COST") Then
x1 = x1 + 1
x2 = x2 + b(i, 7)
c(k, m + u + 1) = x1 & "|" & x2
ElseIf b(1, 7) = WorksheetFunction.Trim("UNIT SALE") Then
y1 = y1 + 1
y2 = y2 + b(i, 7)
c(k, m + u + 2) = y1 & "|" & y2
End If
End If
Next
n = n + 1
Next
'
ReDim e(1 To dic.Count, 1 To UBound(c, 2))
For i = 1 To dic.Count
For j = 1 To 5
e(i, j) = c(i, j)
Next
For j = 6 To 6 + u
e(i, j) = Format(c(i, j), "0.00; -0.00; -")
If e(i, j) = "" Or e(i, j) = 0 Then e(i, j) = "-"
Next
x1 = Split(c(i, m + u + 1), "|")(0)
x2 = Split(c(i, m + u + 1), "|")(1)
If x1 > 0 Then
x3 = x2 / x1
e(i, m + u + 1) = Format(x3, "$#,##0.00; -$#,##0.00; -")
End If
y1 = Split(c(i, m + u + 2), "|")(0)
y2 = Split(c(i, m + u + 2), "|")(1)
If y1 > 0 Then
y3 = y2 / y1
e(i, m + u + 2) = Format(y3, "$#,##0.00; -$#,##0.00; -")
End If
e(i, m + u + 3) = Format((y3 - x3) * e(i, m + u), "$#,##0.00; -$#,##0.00; -")
Next
ListBox1.List = e
End Sub
Private Sub ComboBox1_Change()
With ComboBox1
If .Value = "" Then
Call LoadListbox
Exit Sub
End If
If .ListIndex = -1 Then Exit Sub
ListBox1.Clear
ListBox1.List = Sheets(.Value).Range("A2", Sheets(.Value).Range("H" & Rows.Count).End(3)).Value
End With
End Sub
Private Sub UserForm_Activate()
Call LoadListbox
End Sub
Private Sub UserForm_Initialize()
ComboBox1.AddItem "RPA"
End Sub
thanks in advance
