3 nested loops in an array - VBA

Question

I have 2 nested loops in an array but as far as I understand I need third which I am not able to implement.

I have following data (in yellow):

Current code calculates as indicated in column Actual Behavior:

Dim arr, outarr as Variant
Dim lastc, lastr as long

lastc = 2
lastr = Cells(ws.Rows.count, lastc).End(xlUp).Row
    arr = Range(Cells(2, lastc), Cells(lastr, lastc))
      cnt = ((UBound(arr, 1) - 1) * UBound(arr, 1)) / 2
          k = 1
            ReDim outarr(1 To cnt, 1 To 1)
                For i = LBound(arr, 1) + 1 To UBound(arr, 1)
                       For j = LBound(arr, 1) To i - 1
                             outarr(k, 1) = arr(j, 1) - arr(i, 1)
                                
        k = k + 1
                        Next j
              Next i

Desired behavior would be for values from 1.1 to 6.1 to store minimum value in that range as a result. 10 - 0 = 10 but I need actual minimum value in that range (1.6 to 6.6) and minimum value would be 10 - 40 = -30.

It's really important that minimum value is always calculated as first value of a range - X value of a range. First value in given loop is a constant.

I believe that third loop is needed to store minimum value and then insert this value to outarr but I've not been successful yet.

Thank you for your help.

I'm not sure if I understand the minimum value part correctly.. For the B7-B3, won't the minimum value be 5 - 40? and B7-B2, the minimum value would be 0 - 40? — Raymond Wu
– Raymond Wu, Commented Dec 1, 2021 at 9:53
@RaymondWu Updated my query. Thanks for pointing that out. First value in a range is a constant, calculation is based on (first value - X value, first value - Y value .....). — Thayskills
– Thayskills, Commented Dec 1, 2021 at 10:05
Sorry, I'm still not getting it, for B5-B3, the output is 25 but isn't the minimum 30 - 40? — Raymond Wu
– Raymond Wu, Commented Dec 1, 2021 at 10:19
@RaymondWu Yes, you are correct. I just didn't calculate that one since desired behavior is explained in B7 example. But yes, minimum would be - 10. — Thayskills
– Thayskills, Commented Dec 1, 2021 at 10:39
@RaymondWu one more question Raymond. This loop is part of bigger loop and when I'm attempting to access next loop outputindex is growing by 1. Any idea why? Number of rows, array size, everything is identical. Thank you. — Thayskills
– Thayskills, Commented Dec 4, 2021 at 12:56

Raymond Wu · Accepted Answer · 2021-12-02 03:23:18Z

1

Instead of making another loop, I keep a record of the lowest value within each loop and compare against the new value in the inner loop:

Option Explicit

Private Sub Test()
    Const startRow As Long = 2
    Const valueCol As Long = 2
    Const outputCol As Long = 4
    
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets("Sheet1")
        
    Dim lastRow As Long
    lastRow = ws.Cells(ws.Rows.Count, valueCol).End(xlUp).Row
    
    Dim inputArr As Variant
    inputArr = ws.Range(ws.Cells(startRow, valueCol), ws.Cells(lastRow, valueCol)).Value
    
    Dim outputSize As Long
    outputSize = ((UBound(inputArr, 1) - 1) * UBound(inputArr, 1)) / 2
    
    Dim outputIndex As Long
    Dim outputArr As Variant
    ReDim outputArr(1 To outputSize, 1 To 1) As Variant
    
    Dim i As Long
    Dim n As Long
    
    Dim currFirst As Long
    Dim currLowest As Long
    
    For i = 2 To UBound(inputArr, 1)
        currFirst = inputArr(i, 1)
        currLowest = currFirst - inputArr(i - 1, 1)
                
        For n = i - 1 To 1 Step -1
            Dim testLowest As Long
            testLowest = currFirst - inputArr(n, 1)
            
            If testLowest < currLowest Then currLowest = testLowest
            
            outputIndex = outputIndex + 1
            outputArr(outputIndex, 1) = currLowest
        Next n
    Next i
    
    ws.Cells(startRow, outputCol).Resize(UBound(outputArr, 1)).Value = outputArr
End Sub

answered Dec 2, 2021 at 3:23

Raymond Wu

3,4272 gold badges9 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Thayskills Over a year ago

Thank you Raymond. Had to amend certain parts of code to meet my needs but works perfectly.

Samuel Everson · Accepted Answer · 2021-12-01 15:39:18Z

I believe this outputs the desired results.

Rather than adding a 3rd nested loop to the mix I've added an additional helper loop into the first level.

Please note: This should give you a good idea how to approach this with less complexity than another layer of For Next loop but due to positioning, performance will degrade as the iterations grow.

I've renamed some variables to be more descriptive (except x because I got lazy).

Explation is below the code, here is the re-worked loop section.

OutputCounter = 1
For LayerOneStep = LBound(ValueArray, 1) To UBound(ValueArray, 1)
    ReDim TempArray(1 To LayerOneStep)
    For x = 1 To UBound(TempArray)
        TempArray(x) = ValueArray(x, 1)
    Next x
    For LayerTwoStep = LBound(ValueArray, 1) To LayerOneStep - 1
        MaxValue = WorksheetFunction.Max(TempArray)
        TempValue = (ValueArray(LayerOneStep, 1) - ValueArray(LayerTwoStep, 1))
        If ValueArray(LayerOneStep, 1) = 0 Or ValueArray(LayerTwoStep, 1) = 0 Then
            OutputArray(OutputCounter) = TempValue
            OutputCounter = OutputCounter + 1
        ElseIf TempValue < ValueArray(LayerOneStep, 1) - MaxValue Then
            OutputArray(OutputCounter) = TempValue
            OutputCounter = OutputCounter + 1
        Else
            OutputArray(OutputCounter) = ValueArray(LayerOneStep, 1) - MaxValue
            OutputCounter = OutputCounter + 1
        End If
    Next LayerTwoStep
Next LayerOneStep

TempArray() is used to store your grouped values. I.e. B3 & B2 or B5 & B4 & B3 & B2, etc. This is achieved by assigning the values from Column B row 2 to whatever the LayerOne counter is up to row. The values are assigned from the existing ValueArray array rather than accessing the worksheet again each time.
Assigning it this way allows us to use the WorksheetFunction.Max() function to find the largest number in the set. (keep in mind, as above, not the most performance based way)
We then use some variables to assign some calculations to. The MaxValue as above and TempValue being the x-y approach - same outcome as the worksheet formulas.
We then us some If...Then...Else logic to start working out the absolute minimum value based on the calculation range.
Fist If conditions are checking if either value as part of the TempValue calculation is zero (0). If it is, it returns some funky results so this ensures we go with the x - 0 or 0 - y outcome to the OutputArray.
Second is checking if the TempValue calculation evaluates to less than x - MaxValue. If Yes it returns TempValue to the OutputArray.
Else, we return x - MaxValue to the OutputArray.

Outcome of the above code;

Collectives™ on Stack Overflow

3 nested loops in an array - VBA

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related