Hi,
I got the following code thanks to MickG here on this forum, which helped me a lot! It worked on Excel on Windows, but on the Mac, where it's gonna be used, it doesn't..
I imported the Dictionary.cls and KeyValuePair.cls from https://sysmod.wordpress.com/2011/11...ng-dictionary/, but I can't get it to work.. any idea what to do to make it work?
Here's the original code:
Any idea what to add/replace to call that dictionary/[/FONT]KeyValuePair?[/FONT]
I got the following code thanks to MickG here on this forum, which helped me a lot! It worked on Excel on Windows, but on the Mac, where it's gonna be used, it doesn't..
I imported the Dictionary.cls and KeyValuePair.cls from https://sysmod.wordpress.com/2011/11...ng-dictionary/, but I can't get it to work.. any idea what to do to make it work?
Here's the original code:
Code:
[/COLOR][COLOR=#333333][FONT=Georgia]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR][COLOR=#333333][FONT=Georgia]Dim Dn As Range
Dim Rng As Range
Dim nRng As Range
Dim Dic As Object
Dim Ac As Long
Dim k As Variant
Dim p As Variant, Sp As Variant
Dim c As Long, Wk As Long, n As Long
With Sheets(“Sheet1”)
Set Rng = .Range(“C3”, .Range(“C” & Rows.Count).End(xlUp))
End With
Set nRng = Rng.Offset(, 1).Resize(, 52)
If Not Intersect(nRng, Target) Is Nothing Then
Set Dic = CreateObject(“Scripting.Dictionary”)
Dic.CompareMode = 1
For Each Dn In Rng
For Ac = 1 To 52
If Dn.Offset(, Ac).Value “” Then
If Not Dic.exists(Dn.Offset(, Ac).Value) Then
Set Dic(Dn.Offset(, Ac).Value) = CreateObject(“Scripting.Dictionary”)
End If[/FONT][/COLOR]
[COLOR=#333333][FONT=Georgia]If Not Dic(Dn.Offset(, Ac).Value).exists(Dn.Value) Then
Dic(Dn.Offset(, Ac).Value).Add (Dn.Value), Rng(1).Offset(-1, Ac).Value
Else
Dic(Dn.Offset(, Ac).Value).Item(Dn.Value) = Dic(Dn.Offset(, Ac).Value).Item(Dn.Value) _
& “, ” & Rng(1).Offset(-1, Ac).Value
End If
End If
Next Ac
Next Dn[/FONT][/COLOR]
[COLOR=#333333][FONT=Georgia]With Sheets(“Sheet2”)
.Range(“A:B”).ClearContents
For Wk = 1 To 52
For Each k In Dic.Keys
If Val(k) = Wk Then
c = c + 1
.Cells(c, 1) = “Week ” & k
c = c + 1
For Each p In Dic(k)
.Cells(c, 1) = p
Sp = Split(Dic(k).Item(p), “, “)
For n = 0 To UBound(Sp)
.Cells(c, 2) = Sp(n)
c = c + 1
Next n
Next p
End If
Next k
Next Wk
End With
End If
End Sub[FONT=Verdana]
Any idea what to add/replace to call that dictionary/[/FONT]KeyValuePair?[/FONT]