Ok, where to begin.. this is going to get a bit technical, so hopefully you can follow along.
That line will loop through the array named "arr" from the first entry to the last. In the line before that, I did a mass-move of data from A1:A?? into a two-dimensional horizontal array [arr(1,1) being the first value in that array, arr(2, 1) being the second, etc]. UBound stands for Upper Bound.
.Range("B" & i & ":E" & i).Value = Split(arr(i, 1), "-")
From the previous line, we're now looping through the array item by item starting at 1. So when i = 1, this line of code is essentially
.Range("B1:E1").Value = Split(arr(1, 1), "-")
arr(1, 1) refers to the first entry in the array, for example, "12-1-2-4" from cell A1.
Using the Split function splits that one entry into, in this case, four separate entries using "-" as the delimeter. So instead of having one array entry with "12-1-2-4", I now have four separate values in a temporary array, consisting of 12, 1, 2 and 4. Since I've referred to 4 individual cells to the left of the = sign (B1:E1), I am simply assigning those four cells the four values of the temporarily split array value.
Because the array is horizontal (it's created that way when you first load the values into the array even though it came from values going down a column), when you paste the array back to the worksheet you can paste it directly into a horizontal range containing the same number of cells as in the array (or temporary array in this case). If I had wanted to return the values to the worksheet in one column (e.g. B1:B4), I would need to Transpose the array (change it from horizontal to vertical) by using
.Range("B1:B4").Value = Application.Transpose(Split(arr(i, 1), "-"))
After that first line is done, we loop and i changes to 2. So we're then looking at the second value in the original array, as well as referring to the second row on the worksheet. Had the worksheet data started in a different row, like 20, we could have adjusted the code slightly to take that into account when adding the newly split data back to the sheet.
Hopefully that helps in some way!
Bookmarks