CODEWITHSUNDEEP
vbaexcel
Cjaspire
Cjaspire

Reputation: 3

Put dictionary key and item in columns without transposing

I have a macro that creates a dictionary with over 30k entries, My problem now is that I would like to have these entries show in columns in excel Excel has a maximum number of column entries that doesn't allow me to put my keys in and then transpose them. I would like all the keys to be in one column and all the values in another.

Question: Is there a way around this?

Upvotes: 0

Views: 444

Answers (1)

Chris
Chris

Reputation: 308

Sub HTH()
 Dim vArray As Variant
 Dim lLoop As Long
 Dim rCell As Range

 With CreateObject("Scripting.Dictionary")
    .CompareMode = 1
    For Each rCell In Range("B1:I3022")
        vArray = Split(rCell.Value, " ")
        For lLoop = LBound(vArray) To UBound(vArray)
            If Not .Exists(vArray(lLoop)) Then
                .Add vArray(lLoop), 1
            Else
                .Item(vArray(lLoop)) = .Item(vArray(lLoop)) + 1
            End If
        Next lLoop
    Next rCell
    MsgBox ("there are " & .Count & "Keys")
    Dim keyArray, itemArray, resultArray
    keyArray = .Keys
    itemArray = .Items
    ReDim resultArray(LBound(keyArray) To UBound(keyArray), 0 To 1)
    For i = LBound(keyArray) To UBound(keyArray)
        resultArray(i, 0) = keyArray(i)
        resultArray(i, 1) = itemArray(i)
    Next i
    Range("L1").Resize(UBound(resultArray) + 1, 2) = resultArray
 End With
End Sub

Upvotes: 1

Related Questions