This one has me stumped.
See attached workbook:
Using a dictionary I would like to copy the values in Sheet1 column C to Sheet 2 column A based upon the contiguous groups of strings in Sheet1 column A. I.E. Sheet1 C1 will be copied to Sheet2 A1, Sheet1 C7:C9 will be transposed to Sheet2 A2:C2, etc. (See sheet2 for results)
Note: I know many other ways of doing this, I am specifically looking to see if this is possible using a dictionary.
Code I have so far
Sub Dictionary_Test()
Dim ws1 As Worksheet: Set ws1 = Sheets("Sheet1")
Dim ws2 As Worksheet: Set ws2 = Sheets("Sheet2")
Dim r As Range
Dim dict As Object
Dim i As Long
Dim it
Set dict = CreateObject("Scripting.Dictionary")
i = 1
With dict
For Each r In ws1.Range("A2:A" & ws1.Range("A" & Rows.Count).End(xlUp).Row)
'// The second if in the below line is to account for single string in group and the third if is to bypass the rest of the group
If Not Len(r) = 0 And Not Len(r.Offset(1, 0)) = 0 And Not Len(r.Offset(-1)) = 0 Then
'// Add item to dictionary: key is i, item is range
On Error GoTo Err1
Set .Items(i) = r.Resize(r.End(xlDown).Row - r.Row, 1) 'FIRST ERROR IS HERE: 424 Object Required
i = i + 1
Debug.Print r.Address
Else
'Specify for single string in group
End If
Next r
'// My thoughts on how I would proceed
For Each it In .Keys
ws2.Range("A" & Rows.Count).End(3)(2) = Application.Transpose(.Keys(it))
Next it
End With
Exit Sub
Err1:
Debug.Print Err.Number & " " & Err.Description
End Sub
If you need any other information please let me know. Thank you.
Bookmarks