Output dictionary contents to sheet

[Trebor8484]

Hi, I am trying to output the contents of a dictionary to a worksheet.

I need to populate my dictionary into columns A and B into the last unused rows in both columns.

The dictionary will have an even number of items, so for example item 1,3,5,7,9 would go into column A and 2,4,6,8,10 into column B.

Any suggestions please?

For Each rng In Sht.Range("D6:BM" & Sht.Cells(Rows.Count, "B").End(xlUp).Row).Columns
        For Each c In rng.Cells
            If c.Value = 0 Then
                Cells(c.Row, c.Column).Interior.Color = 7303929

                x = x + 1
                dict.Add x, Cells(5, c.Column)
                x = x + 1
                dict.Add x, Cells(c.Row, 2)
            End If
        Next c
    Next rng

 

[Norie]

The only way I can see you doing that is using a loop.

Dim rngDst As Range
Dim idx As Long

    Set rngDst = Range("A1").
  
    For idx = 1 To dict.Count Step 2
        rngDst = dict(idx)
        rngDst.Offset(,1) = dict(idx+1)
        Set rngDst = rngDst.Offset(1)
    Next idx

 

[Fluff]

How about

   For Each rng In sht.Range("D6:BM" & sht.Cells(Rows.Count, "B").End(xlUp).Row).Columns
        For Each c In rng.Cells
            If c.Value = 0 Then
                Cells(c.Row, c.Column).Interior.Color = 7303929

                x = x + 1
                dict.Add x, Array(Cells(5, c.Column), Cells(c.Row, 2))
            End If
        Next c
    Next rng
    sht.Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(dict.Count, 2).Value = Application.Index(dict.items, 0)

 

[Trebor8484]

Thanks, both of these suggestions work.

Fluff, I went with yours as it was a one line addition to my existing code. I do have a slight problem though where the dictionary output is converting my dates to American format. I have tried the below amendment but it doesn’t solve my issue.

dict.Add x, Array(Cells(5, c.Column), Int(CDate(Cells(c.Row, 2))))

 

[Fluff]

Try

dict.Add x, Array(Cells(5, c.Column), CLng(Cells(c.Row, 2)))

 

[Trebor8484]

Still the same unfortunately.

I think it may be at the point where it outputs the data to the sheet when they are converted.

 

[Fluff]

Using Clng works for me, check that the column is formatted as general, before running the code.

 

[Trebor8484]

Thanks, by setting my column to general then back to date solved the issue.

 

[Fluff]

Glad to help & thanks for the feedback.