Offset

B

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]
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Sort by date Sort by votes
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.
 
Upvote 0
billandrew said:
Good Afternoon

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

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:
Upvote 0
@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:
Upvote 0
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)
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question Question
How to pass variable between subs that are stored as array element through CreateObject("Scripting.Dictionary")
Replies
1
Views
477
Micron
M
WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
66
RoryA
RoryA
C
  • Solved
Type Mismatch on Dictionary Elements
Replies
2
Views
457
Ctsmiths192
C
H
  • Question Question
VBA split file based and send splitted file in separate emails
Replies
0
Views
706
harmless92
H
S
  • Question Question
Excel VBA for Yahoo Finance
Replies
5
Views
577
alansidman
A

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top