Offset

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
Good Afternoon

Could someone explain (May be missing something) why the print to worksheet is in Column E

Code:
[TABLE="width: 62"]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]<colgroup><col width="83" style="width: 62pt;">[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT]<tbody>[TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="width: 83, bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]Sub  SplitColA()[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]Dim i&[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]Dim Cola() As Variant[/COLOR][/SIZE][/FONT]
[/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]Dim dict As Variant[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]Set dict =  CreateObject("scripting.dictionary")[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]    Cola = Range("A1",  Range("A" & Rows.Count).End(xlUp))    [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]    For i = LBound(Cola) To UBound(Cola)[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]        dict.Add i, Split(Cola(i, 1), "  ")[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]    Next i[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]        [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]        With  Sheets("Sheet5").Cells(1, 3)[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]        For Each Key In dict.keys            [/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]    .Range(.Offset(Key, 0), .Offset(Key,  UBound(dict.Item(Key)))) = dict.Item(Key)[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]        Next Key[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]    End With[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000]  [/COLOR][/SIZE][/FONT][TD="bgcolor: transparent"][FONT=Arial][SIZE=3][COLOR=#000000]End Sub[/COLOR][/SIZE][/FONT][/TD]
[FONT=Times New Roman][SIZE=3][COLOR=#000000] [/COLOR][/SIZE][/FONT][/TR]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT]</tbody>[/TABLE]
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,161
Office Version
365
Platform
Windows
It's because your using the offset inside the range, try it like
Code:
    .Offset(Key, 0).Resize(1, UBound(dict.Item(Key))) = dict.Item(Key)
Also it's best to avoid using VBA keywords (such as Key) for the names of variables.
 

BlakeSkate

Well-known Member
Joined
Jan 26, 2015
Messages
500
Office Version
2016
Platform
Windows
Good Afternoon

Could someone explain (May be missing something) why the print to worksheet is in Column E
where are you trying to have it offset?
because of the with statement you're basically having this, if it explains anything to you.

Code:
Sheets("Sheet5").Cells(1, 3).Range(Sheets("Sheet5").Cells(1, 3).Offset(Key, 0), Sheets("Sheet5").Cells(1, 3).Offset(Key,  UBound(dict.Item(Key)))) = dict.Item(Key)
which i'm unsure of why you have anything the way it is.
it prints in E because thats where the offset based on C1 is telling it to go?

edit: nvm fluff figured out what you were attempting to do
 
Last edited:

billandrew

Well-known Member
Joined
Mar 9, 2014
Messages
743
@BlakeSkate Thanks for the explanation understood.@ FluffThanks for the code - however the results are not including the last text in the string.Ex.John Michael Paul Smith. Smith is excluded.
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,161
Office Version
365
Platform
Windows
That's because Split will always give you a Zero based array, so you need to add 1 to the Ubound
Code:
    .Offset(Ky, 0).Resize(1, UBound(dict.Item(Ky)) + 1) = dict.Item(Ky)
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,161
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Forum statistics

Threads
1,077,738
Messages
5,335,921
Members
399,057
Latest member
mgabr

Some videos you may like

This Week's Hot Topics

Top