PDA

View Full Version : [SOLVED] Help for interpretation of the code



olegvolf
12-18-2018, 12:02 AM
Hello
I received a code that doing an unstacking action
from one column into multiply columns

I need help interpretation the rows in the code
in order to understand what the code does
thank you

Sub SplitInto15CellsPerColumn()Dim X As Long, LastRow As Long, vArrIn As Variant, vArrOut As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row
vArrIn = Range("A1:A" & LastRow)
ReDim vArrOut(1 To 3, 1 To Int(LastRow / 3) + 1)
For X = 0 To LastRow - 1
vArrOut(1 + (X Mod 3), 1 + Int(X / 3)) = vArrIn(X + 1, 1)
Next
Range("B1").Resize(3, UBound(vArrOut, 2)) = vArrOut
End Sub

Logit
12-18-2018, 04:51 PM
.
Here is a very much layman's explanation of the code lines. I am certain there is a more technical description that someone else can provide :



Sub SplitInto15CellsPerColumn()
Dim X As Long, LastRow As Long, vArrIn As Variant, vArrOut As Variant
LastRow = Cells(Rows.Count, "A").End(xlUp).Row 'define last used row in Col A
vArrIn = Range("A1:A" & LastRow) 'place all data in Col A used range into an array
ReDim vArrOut(1 To 3, 1 To Int(LastRow / 3) + 1) 'going to look at every third row, in groups of three
For X = 0 To LastRow - 1 'begin process of selection
vArrOut(1 + (X Mod 3), 1 + Int(X / 3)) = vArrIn(X + 1, 1) 'set the data selected to be entered into Col B rows
Next 'repeat selection process until all data reviewed
Range("B1").Resize(3, UBound(vArrOut, 2)) = vArrOut 'enter selected data into Col B rows
End Sub

Logit
12-18-2018, 04:55 PM
.
As can see from the example below ... where I entered A1, A2, A3 .... etc ... up to A19 in column A, then ran the macro :


B
C
D
E
F
G
H

1A1A4A7A10A13A16A19



2A2A5A8A11A14A17



3A3A6A9A12A15A18


The macro first goes down Col A and selects every third entry.

Then it goes thru Col A again, for the rows that were not selected for extraction the first time and selects every third entry again. If it makes it easier to understand,
say the macro removes all the first selections and discards them. Then it removes all the blank rows from what was selected. Then it goes thru the Col A remaining list again.

Then in this example, the macro goes thru the Col A list again and selects the remainder.

I haven't tried a longer list, but I presume if you were to have a Col A list of say 100 rows, the total times the macro would goes thru the Col A data would be more than three times.

olegvolf
12-26-2018, 01:37 AM
Thank You

Logit
12-26-2018, 09:20 AM
.
You are welcome.