Using this code below gives me a result of a unique customer codes base on Calculation sheet. However, I want to get my result base on the list that I have in Solution Sheet. Also want to run the macro in button inside Solution sheet. Any help will be appreciated.
Calculation Sheet
Solution Sheet
Calculation Sheet
Solution Sheet
VBA Code:
Public Sub cTotal()
Dim arr, arr2, arr3
Dim Calc As Worksheet: Set Calc = Worksheets("Calculation")
Dim Sol As Worksheet: Set Sol = Worksheets("Solution")
Dim x As Long, i As Long, a As Long, c As Long, ct As Long
Dim GIVMM As Single, MSU As Double, Cases As Double
arr = Calc.Range("B2:H" & Cells(Rows.Count, 1).End(xlUp).Row)
arr2 = arr
With CreateObject("Scripting.Dictionary")
For x = LBound(arr) To UBound(arr)
If Not IsMissing(arr(x, 1)) Then .Item(arr(x, 1)) = 1
Next
arr = .Keys
End With
ReDim arr3(1 To UBound(arr) + 1, 1 To 7)
c = 1: ct = 1
For i = 0 To UBound(arr)
For a = 1 To UBound(arr2)
If arr2(a, 1) = arr(i) Then
arr3(i + 1, c) = arr(i)
arr3(i + 1, c + 1) = ct
ct = ct + 1
GIVMM = GIVMM + arr2(a, 5)
arr3(i + 1, c + 2) = GIVMM
MSU = MSU + arr2(a, 6)
arr3(i + 1, c + 3) = MSU
Cases = Cases + arr2(a, 7)
arr3(i + 1, c + 4) = Cases
End If
Next
ct = 1: GIVMM = 0: MSU = 0: Cases = 0
Next
Sol.Range("B6").Resize(UBound(arr3, 1), UBound(arr3, 2)) = arr3
End Sub