Find Minimum Value from a dynamic range

[NV]

Hi Friends, please help with my requirement.  Each block is having some Data.  As and when there is a value in the column M, then it should pick the Min/Max value from the next block.  For example, in the above Data, as soon as the formula finds a value in Colum M (8 in this case), then immediately it should pick the Min/Max value from BLOCK 2 and print it in the block 2 where i put question Mark (?).  Once it finds 6, it should pick the value from Block 3.  Since these blocks are not fixed range, unable to hardcode the range.  Hence, Please help with the right formula.  Thanks in advance.

[#2]

@NV 

Sub min()

Dim i As Long
Dim j As Long
Dim k As Long
Dim maxrow As Long

Range("O:O").Clear

Cells(5, 15).Value = "Min"

maxrow = Cells(Rows.Count, 8).End(xlUp).Row

For i = 6 To maxrow

If Cells(i, 13) <> "" Then
j = i + 1

For k = j To maxrow

If Cells(k, 13) <> "" Then

Cells(k, 15).Value = Application.WorksheetFunction.min(Range(Cells(j, 8), Cells(k, 12)))
Exit For

Else
End If

Next k

Else
End If

Next i

End Sub

Maybe with this code. In the attached file you can click the button in cell Q3 to run the macro.

[#3]

Great thanks for the reply. I'll test it out and keep you posted the update. Thanks again.

[#4]

@NV 

I use 365 formula solutions for Excel solutions when possible.  The starting point is to identify any given block by index (thick block lines communicate structure to the viewer but are difficult to maintain and not easily handled by formula). 

Blockλ 
= LAMBDA(b,
    LET(
        n, ROWS(dataSet),
        seqNo, SEQUENCE(n),
        blockNo, SCAN(0,seqNo,
            LAMBDA(acc, s, IF(ISTEXT(INDEX(BlockID, s)), acc + 1, acc))
        ),
        DROP(FILTER(dataSet, blockNo = b), , -1)
    )
);

BlockMinλ 
= LAMBDA(b, MIN(Blockλ(b)));

The first function scans the block labels incrementing the index column whenever it finds a label.  That allows each block of values to be identified and the second function returns the minimum over the block.  A similar function is used to locate the value that shows whether the next block is to be displayed.

[#5]

I felt that i should have given more details on my requirement. Unfortunately, this is not fulfilling my requirement. Hence I've raised another ticket with more details, pls help.