Help Needed: Excel Array

[Cardano]

Ah, I see what you are saying, that may be the best way of possibly achieving what I want.
Thanks for persevering with me guys, your time is much appreciated

[spreadbetting]

I'm obvoiusly struggling to explain what I'm trying to do

For each row in turn I want to subtract that row from the array and then get the smallest or second smallest ( it doesn;t really matter ) from the remaining rows of the array e.g as follows
SMALL(ARRAY-Row(1),2)
SMALL(ARRAY-Row(2),2)
SMALL(ARRAY-Row(3),2)
etc

Can you subtract rows from arrays ?

I'm still confused as to what you're after and there are probably lots of easier ways to do it but it is easy enough to loop thru an array omitting the current row simply by using nested loops like so

Code: Select all

Sub OmitRow()
Dim minValue, Rng1

For x = 1 To 16
    For y = 1 To 16
         
    If y = x Then ' here we do whatever calculations you want on the current row
    
        If x = 1 Then
        Set Rng1 = Range("A2:F16")
        ElseIf x = 16 Then
        Set Rng1 = Range("A1:F15")
        Else
        Set Rng1 = Range("A1:F" & x - 1 & ",A" & x + 1 & ":F16")
        End If
        minValue=WorksheetFunction.Min(Rng1)
        Range("G" & x).Value = "Minimum number outside row " & x & "  is " & minValue
     
    End If
    
    Next y
   
 Next x

End Sub