# Thread: VBA to Separate Values Thanks: 0 Likes:  3 Post #5294180 (1)Post #5294171 (1)Post #5294178 (1)

1. ## VBA to Separate Values

Hi.
Please, in case is possible, I have a dynamic array five columns, the first column is only two words alternated and the other four columns are numbers (grades).
what I need?, I need to separate the rows of math from the row of geography and convert in two different arrays.
 math 40 80 20 60 geog 50 50 50 50 math 79 30 19 68 geog 87 40 10 54 math 65 78 91 40 geog 32 45 58 71 math 21 12 30 60 geog 54 23 80 39 math 54 56 58 60 geog 87 89 91 93 math 19 32 45 58 geog 37 65 93 30 math 77 98 91 40
So, I would like to separate math from geography. thanks.

2. ## Re: VBA to Separate Values

If your data is in A1:E13, you would fill across and down starting in G1 with:

Code:
=INDEX(A:A,ROW(A1)*2-1)
Fill that across from, say, G1:K1 and then down. Then the math is no longer found, if you've filled down all 13 rows, you'll get 0s.
You could remove them in the formula or format the cells not to show 0s.

In M1:Q1, a similar formula:

Code:
=INDEX(A:A,ROW(A1)*2)
Hope this helps or at least gives you an idea of an approach to get what you want.

3. ## Re: VBA to Separate Values

Kweaver, I really Appreciate your input, Thank you. I am really interesting about to see how this kind of ideas are implemented on the Golden Language VBA.Thank you for your time. My apology.

4. ## Re: VBA to Separate Values

Hi

If I understand correctly this is a vba question.

There are no vba native functions to deal with splitting an array.

In the case you posted, what you can do is to use a worksheet function.

Assuming the name of your array is vArray, try:

Code:
,,,
Dim vArray1 As Variant, vArray2 As Variant
Dim lN As Long ' Number of elements in the array
...

lN = UBound(vArray) - LBound(vArray) + 1

vArray1 = Application.Index(vArray, Evaluate("2*row(1:" & ((lN + 1) \ 2) & ")-1"), Array(1, 2, 3, 4, 5))
vArray2 = Application.Index(vArray, Evaluate("2*row(1:" & (lN \ 2) & ")"), Array(1, 2, 3, 4, 5))
...

5. ## Re: VBA to Separate Values

A possible VBA could be:

Code:
Sub SeparateRows()
Dim LR As Long, zM As Integer, zG As Integer, i As Integer
LR = Cells(Rows.Count, "A").End(xlUp).Row
zM = 1
zG = 1
For i = 1 To LR
If Range("A" & i) = "math" Then
Range("S" & zM & ":W" & zM) = Range("A" & i & ":E" & i).Value
zM = zM + 1
Else ' Range("A" & i) = "geog" Then
Range("Y" & zG & ":AC" & zG) = Range("A" & i & ":E" & i).Value
zG = zG + 1
End If
Next i
End Sub
Of course, adjust where you want the results (I put them in S:W and Y:AC)

6. ## Re: VBA to Separate Values

Thank you pgc 01, It is good to know the limits of the Golden language VBA.

7. ## Re: VBA to Separate Values

Mr. kweaver, Thank you again for your time.

8. ## Re: VBA to Separate Values

Originally Posted by pgc01
Hi

If I understand correctly this is a vba question.

There are no vba native functions to deal with splitting an array.

In the case you posted, what you can do is to use a worksheet function.

Assuming the name of your array is vArray, try:

Code:
,,,
Dim vArray1 As Variant, vArray2 As Variant
Dim lN As Long ' Number of elements in the array
...

lN = UBound(vArray) - LBound(vArray) + 1

vArray1 = Application.Index(vArray, Evaluate("2*row(1:" & ((lN + 1) \ 2) & ")-1"), Array(1, 2, 3, 4, 5))
vArray2 = Application.Index(vArray, Evaluate("2*row(1:" & (lN \ 2) & ")"), Array(1, 2, 3, 4, 5))
...
If you wanted to save a small amount of typing, you could replace what I highlighted in red with this...

[{1,2,3,4,5}]

9. ## Re: VBA to Separate Values

kweaver The part I like most about your answer is that I can see the clear logical path you took to resolve the problem, and use just a few line as possible, exactly for the Chapter I am now about FOR NEXT loop. Great job.

10. ## Re: VBA to Separate Values

Rick Rothstein Thanks. Your interventions always are a real enlightening