Pasting item values from scripting dictionary

by Victor Lalonde   Last Updated August 14, 2019 18:26 PM

I have a d = CreateObject("Scripting.Dictionary") that it's items are ranges. I would like to paste those items in a blank worksheet and this is the code I have for now :

ThisWorkbook.Sheets(2).ListObjects(1).DataBodyRange.Clear

    With LatestRevisions
        ThisWorkbook.Worksheets(2).Range("A1").Resize(.Count, 1) = Application.Transpose(.keys)
        ThisWorkbook.Worksheets(2).Range("B1").Resize(.Count, 21) = Application.Transpose(.Items)
    End With

In which LatestRevisions is a variable set to my dictionary name d and I resize the range B1 to 21 because that is the size of my item ranges.

My problem is that the items are in the wrong direction (column data is in rows and row data is in columns), but if I do not put in the Application.Transposenothing gets pasted.

Any ideas?

Thank you

Edit : Here is how my dictionary is created and filled

 Set d = CreateObject("Scripting.Dictionary")
If SearchRng.Columns.Count > 1 Then Exit Function

For Each Cell In SearchRng

    If Not d.Exists(Cell.Value) Then
        d.Add Cell.Value, Cell.Offset(0, 1).Resize(1, 21) 'fetch data from column B:V
    Else
        RevisionInDict = ConvertTextToNumeric(d(Cell.Value).Cells(1).Value)
        Revision = ConvertTextToNumeric(Cell.Offset(0, 1))
        If Revision > RevisionInDict Then
            Set d(Cell.Value) = Cell.Offset(0, 1).Resize(1, 21)
                End If
        End If
    Next

    Set GetLatestRevisions = d
End Function


Answers 1


The trick is to double transpose

Imagine the following "Sheet2" which I read the ranges from

enter image description here Image 1: "Sheet2" contains example data to fill the items.

The following code …

Option Explicit

Public Sub TestDictionary()
    Dim Dic As Object
    Set Dic = CreateObject("Scripting.Dictionary")

    Dic.Add "key1", Worksheets("Sheet2").Cells(1, 1).Resize(1, 21)
    Dic.Add "key2", Worksheets("Sheet2").Cells(2, 1).Resize(1, 21)
    Dic.Add "key3", Worksheets("Sheet2").Cells(3, 1).Resize(1, 21)
    Dic.Add "key4", Worksheets("Sheet2").Cells(4, 1).Resize(1, 21)
    Dic.Add "key5", Worksheets("Sheet2").Cells(5, 1).Resize(1, 21)
    Dic.Add "key6", Worksheets("Sheet2").Cells(6, 1).Resize(1, 21)


    'output in column A and B
    Worksheets("Sheet1").Range("A1").Resize(Dic.Count, 1).Value = Application.Transpose(dic.keys)
    Worksheets("Sheet1").Range("B1").Resize(Dic.Count, 21).Value = Application.Transpose(Application.Transpose(Dic.items))
End Sub

… produces the following output:

enter image description here Image 2: "Sheet1" for output of keys in column A and items in column B to V.

Pᴇʜ
Pᴇʜ
July 31, 2019 13:21 PM

Related Questions




Get unique Attributes from XML using VBA

Updated August 01, 2018 14:26 PM

VBA dictionary <out of context> after Sub is run

Updated June 12, 2017 10:26 AM

VBA - Creating dictionary of dictionary of arrays

Updated June 25, 2017 22:26 PM