Comparing multi-dimensional arrays in VBA to single column - double loop

[jadeliew]

I want to compare values in a Multid array to a values in a single Column.

Stoploss:

45
54
76

OHLC:

37 43 30 40
32 43 41 43
32 54 76 87

I want to compare each value in Stoploss to OHLC and find the first value that is larger than Stoploss in OHLC, in this case the first number larger than 45 in OHLC will be 54. And this happens for every subsequent number in Stoploss, adjusting the OHLC array to the row number of the value in Stoploss I want to compare. There may be spaces in the range Stoploss. So far i have this:
<code>For i = 63 To 16

Set OHLC = Range("B" & i & ":" & "E" & i)

For Each cll In OHLC



If Range("x" & i).Value > 0 Then
stoploss = Range("x" & i)
End If

If cll.Value > stoploss Then

Range("s" & cll.Row) = cll.Value

End If

Next cll

Next i</code>There is no error message in my code, but no values are returned in column "S". I also know this double looping method doesn't work because it returns the first value in range B:E for each row, which is not what I want. Any help guys? Any input will be very appreciated. Thank you

 

[MickG]

Try this:-
NB:- Range "OHLC" assumed to be in Range "B1:E3" and Range "StopLoss" to start in cell "X1".
Results start in "S1"

[COLOR="Navy"]Sub[/COLOR] MG10Oct27
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, R [COLOR="Navy"]As[/COLOR] Variant, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nRng [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("E" & Rows.Count).End(xlUp))
[COLOR="Navy"]With[/COLOR] Application
    ReDim Ray(.Min(Rng) To .Max(Rng)) [COLOR="Navy"]As[/COLOR] Boolean
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Ray(Dn.Value) = True
    [COLOR="Navy"]Next[/COLOR] Dn
    [COLOR="Navy"]Set[/COLOR] nRng = Range(Range("X1"), Range("X" & Rows.Count).End(xlUp))
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] nRng
            [COLOR="Navy"]For[/COLOR] n = .Min(Rng) To .Max(Rng)
                [COLOR="Navy"]If[/COLOR] Ray(n) And n > Dn.Value [COLOR="Navy"]Then[/COLOR]
                     c = c + 1
                    Cells(c, "S") = n
                    [COLOR="Navy"]Exit[/COLOR] For
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Next[/COLOR] n
        [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick