Private Sub UserForm_Initialize()
Dim cUnique As Collection
Dim Dn As Range
Dim Rng As Range
Dim cell As Range
Dim sh As Worksheet
Dim vNum As Variant
Dim c
Dim oHds As Variant
Dim N As Integer
With Sheets("ReturnData")
Set Rng = .Range(.Range("I6"), .Range("I" & Rows.Count).End(xlUp))
End With
Set cUnique = New Collection
On Error Resume Next
For Each cell In Rng.Cells
cUnique.Add CStr(cell.Value), CStr(cell.Value)
Next cell
On Error GoTo 0
ReDim Ray(1 To cUnique.Count + 1, 1 To 8)
For Each vNum In cUnique
c = c + 1
Ray(c, 1) = vNum
For Each Dn In Rng
If Dn = Val(vNum) Then
If IsEmpty(Ray(c, 2)) Then Ray(c, 2) = Dn.Offset(, -5)
Select Case Dn.Offset(, -4)
Case "Receive": Ray(c, 3) = Ray(c, 3) + 1
Case "Return": Ray(c, 4) = Ray(c, 4) + 1
Case "Relocate": Ray(c, 5) = Ray(c, 5) + 1
Case "Lost": Ray(c, 6) = Ray(c, 6) + 1
Case "Damaged": Ray(c, 7) = Ray(c, 7) + 1
End Select
Ray(c, 8) = Ray(c, 3) - (Ray(c, 4) + Ray(c, 6))
End If
Next Dn
Next vNum
With Me.lbPOList
.List = Ray
.Font.Size = 12
.ColumnCount = 8
End With
Dim ncUnique As Collection
c = 0
With Sheets("ReturnData")
Set Rng = .Range(.Range("H6"), .Range("H" & Rows.Count).End(xlUp))
End With
Set ncUnique = New Collection
On Error Resume Next
For Each cell In Rng.Cells
ncUnique.Add CStr(cell.Value), CStr(cell.Value)
Next cell
On Error GoTo 0
ReDim nRay(1 To ncUnique.Count + 1, 1 To 9)
For Each vNum In ncUnique
c = c + 1
nRay(c, 1) = vNum
For Each Dn In Rng
If Dn = vNum Then
If IsEmpty(nRay(c, 2)) Then nRay(c, 2) = Dn.Offset(, -4)
Select Case Dn.Offset(, -3)
Case "Receive": nRay(c, 3) = nRay(c, 3) + 1
Case "Relocate": nRay(c, 4) = nRay(c, 4) + 1
Case "Return": nRay(c, 5) = nRay(c, 5) + 1
Case "Lost": nRay(c, 7) = nRay(c, 7) + 1
Case "Damaged": nRay(c, 8) = nRay(c, 8) + 1
End Select
End If
Next Dn
For Each Dn In Rng.Offset(, 2)
If Dn = vNum Then
Select Case Dn.Offset(, -5)
Case "Relocate": nRay(c, 6) = nRay(c, 6) + 1
End Select
End If
Next Dn
nRay(c, 9) = (nRay(c, 3) + nRay(c, 4)) - (nRay(c, 5) + nRay(c, 6) + nRay(c, 7))
Next vNum
With Me.lbUnitList
.List = nRay
.Font.Size = 12
.ColumnCount = 9
End With
End Sub
Bookmarks