Results 1 to 10 of 10

Thread: VBA to Separate Values

  1. #1
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Thank you for reading this.
    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. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    633
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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.
    Last edited by kweaver; Jun 13th, 2019 at 08:21 PM.

  3. #3
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    19,706
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    2 Thread(s)

    Default 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))
    ...
    Last edited by pgc01; Jun 13th, 2019 at 08:49 PM.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  5. #5
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    633
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #6
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Separate Values

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

  7. #7
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Separate Values

    Mr. kweaver, Thank you again for your time.

  8. #8
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,251
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: VBA to Separate Values

    Quote Originally Posted by pgc01 View Post
    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}]
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

  9. #9
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #10
    Board Regular
    Join Date
    Jan 2011
    Location
    orlando
    Posts
    358
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to Separate Values

    Rick Rothstein Thanks. Your interventions always are a real enlightening

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •