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
I tried something like below instead of the Text to Columns but I am getting type mismatch error
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
VBA Code:
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
VBA Code:
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 |