Using Split for all array elements without a loop

[mse330]

Hello all,

Is it possible to split all elements of an array at once without a loop ?

I have the below table where I have different staff name, their supervisors at the shift & number of shifts. I have written the below code to get the count of each unique staff/supervisor/shift which works fine but I was wondering if I could split my Keys by ";" through the whole array without a loop. In my situation below, I used text to column & it did the job but I was wondering if I could do it through VBA without a loop perhaps using Application.Index but I am getting an error

Sub test()

Dim a, Txt As String
a = Cells(1).CurrentRegion

With CreateObject("scripting.dictionary")
   For x = 2 To UBound(a)
      Txt = Join(Array(a(x, 1), a(x, 2), a(x, 3)), ";")
      If Not .exists(Txt) Then .Add Txt, a(x, 4) Else .Item(Txt) = .Item(Txt) + a(x, 4)
   Next
   Range("F2").Resize(.Count) = Application.Transpose(.keys)
   Range("F2").Resize(.Count).TextToColumns [F2], 1, , , , 1
   Range("i2").Resize(.Count) = Application.Transpose(.items)
End With

End Sub

I tried something like below instead of the Text to Columns but I am getting type mismatch error

Range("F2").Resize(.Count, 3) = Application.Index(Application.Transpose(Split(.keys, ";")), 0, Array(1, 2, 3))

Book1
	A	B	C	D	E	F	G	H	I
1	Staff Name	Supervisor	Shift	No of shift		Staff Name	Supervisor	Shift	No of shift
2	Daniel	David	Night	1		Daniel	David	Night	1
3	Willian	Luke	Morning	6		Willian	Luke	Morning	12
4	Jacob	Luke	Night	3		Jacob	Luke	Night	7
5	James	David	Morning	6		James	David	Morning	6
6	Jacob	Luke	Morning	3		Jacob	Luke	Morning	3
7	James	David	Night	6		James	David	Night	6
8	Daniel	John	Morning	4		Daniel	John	Morning	4
9	James	Luke	Night	2		James	Luke	Night	6
10	Jacob	Luke	Night	4		Jacob	David	Morning	1
11	Willian	Luke	Morning	6		James	John	Night	2
12	Jacob	David	Morning	1		Daniel	Matthew	Night	6
13	James	John	Night	2
14	Daniel	Matthew	Night	6
15	James	Luke	Night	4
Sheet1

 

[Fluff]

Split requires a string, so cannot work on an array.

 

[mse330]

Thanks Fluff ... So I either have to loop in VBA or just go with Text To Columns like I did

 

[Fluff]

That's right, I'd tend to do with the text to columns approach that you used.