Separate every 3 entries into 2 arrays? (or other solution)

[mae0429]

Hi all, need some help:

I'll have an array where every 3 entries, the target value changes. For example: the values of myVar(1) to myVar (3) are around 10, while the values of myVar(4) to myVar(6) are around 20. Entries 7-9 go back to 10, 10-12 go back to 20, and so on...

Now, I'm going to be doing several things with the different sets of data, so I think the easiest way to do this is to make a "low" array and a "high" array, but I don't want to have zeroes as placeholders in the new arrays. Any clue how to do this? Or if there is a simpler way to refer to every 3 entries from the original array?

Thanks,
Matt

[mae0429]

Mod operator perhaps?

[Bob Phillips]

It would have to be a loop as far as I can see, write 3 to one, then 3 to the other, and so on.

[Kenneth Hobs]

Depends on what you need. You can use a For loop and Step by 5 or 6 depending on the array being 0 or 1 based.

e.g.

Option Base 1

Sub test()
  Dim a() As Variant, i As Integer
  a() = [{1,2,3,100,101,102,4,5,6,103,104,105,7,8,9,106,107,108}]
  
  'Show 1-3, 4-6, 7-9
  For i = LBound(a) To UBound(a) Step 6
    Debug.Print a(i), a(i + 1), a(i + 2)
  Next i
End Sub

[mae0429]

Thanks for the replies. Code makes sense, and does as I asked, but I realize I wasn't specific enough when I said "I'll be doing things with them later". There are two main things I'm trying to accomplish:

1) calculate at what point the value jumps outside a 10% tolerance

2) plot the 2 sets of data independently on a chart

I know I can manipulate Kenneth's code to solve issue 1 no problem, but I'm having a harder time envisioning the solution to issue 2 with that code.

My initial thought was to write a function (who knows if I'll need to do this in the future again) something like the following (just started this):

Option Explicit

Function SplitArray(inArray As Variant, groupNum As Long) As Variant
    Dim s, iRow, iCol As Long
    ReDim outArray(1 To 2, 1 To UBound(inArray))
    iRow = 2
    For s = 1 To UBound(inArray)
        If s Mod groupNum = 1 Then
            If iRow = 2 Then
                iRow = 1
            Else
                iRow = 2
            End If
        End If
        outArray(iRow, iCol) = inArray(s)
    Next s
    SplitArray = outArray
End Function

Only trouble is, I can't seem to think of how to increment the iCol variable independently for each dimension (I probably need 2, right?) as well as how to identify the length of the new arrays (there'll be a bunch of empty's at the end with the way I currently have it, but I can't just divide by 2 because they may not be even)

Any ideas?

[mae0429]

Function now works, but it has the bunch of emptys tacked on, any ideas:

Option Explicit

Sub test()
    Dim a(), b As Variant
    a() = [{1,2,3,100,101,102,4,5,6,103,104,105,7,8,9,106,107,108}]
    b = SplitArray(a, 3)
End Sub

Function SplitArray(inArray As Variant, groupNum As Long) As Variant
    Dim s, iRow, iCol, iCol1, iCol2 As Long
    ReDim outArray(1 To 2, 1 To UBound(inArray))
    iCol1 = 1 - groupNum
    iCol2 = 1 - groupNum
    iRow = 2
    For s = 1 To UBound(inArray)
        If s Mod groupNum = 1 Then
            If iRow = 2 Then
                iRow = 1
                iCol1 = iCol1 + groupNum
                iCol = iCol1
            Else
                iRow = 2
                iCol2 = iCol2 + groupNum
                iCol = iCol2
            End If
        End If
        outArray(iRow, iCol) = inArray(s)
        iCol = iCol + 1
    Next s
    SplitArray = outArray
End Function

[Kenneth Hobs]

Redim with Preserve.

[mae0429]

I think this does the trick:

Option Explicit

Sub test()
    Dim a(), b As Variant
    a() = [{1,2,3,100,101,102,4,5,6,103,104,105,7,8,9,106,107,108}]
    b = SplitArray(a, 3)
End Sub

Function SplitArray(inArray As Variant, groupNum As Long) As Variant
    Dim s, iRow, iCol, iCol1, iCol2, upLim, upLim1, upLim2 As Long
    ReDim outArray(1 To 2, 1 To UBound(inArray))
    iCol1 = 1 - groupNum
    iCol2 = 1 - groupNum
    iRow = 2
    For s = 1 To UBound(inArray)
        If s Mod groupNum = 1 Then
            If iRow = 2 Then
                iRow = 1
                iCol1 = iCol1 + groupNum
                iCol = iCol1
            Else
                iRow = 2
                iCol2 = iCol2 + groupNum
                iCol = iCol2
            End If
        End If
        outArray(iRow, iCol) = inArray(s)
        iCol = iCol + 1
    Next s
    For iCol = 1 To UBound(outArray, 1)
        If IsEmpty(outArray(1, iCol)) = True Then
            upLim1 = iCol - 1
            Exit For
        End If
    Next iCol
    For iCol = 2 To UBound(outArray, 2)
        If IsEmpty(outArray(2, iCol)) = True Then
            upLim2 = iCol - 1
            Exit For
        End If
    Next iCol
    upLim = WorksheetFunction.Max(upLim1, upLim2)
    ReDim Preserve outArray(1 To 2, 1 To upLim)
    SplitArray = outArray
End Function

A little more testing and I'll mark as solved.

[mae0429]

Oops:

For iCol = 1 To UBound(outArray, 1)

should be

For iCol = 1 To UBound(outArray, 2)

[Bob Phillips]

Is this what you are trying to do?