Solved: 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 lopp 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?