Using the "usedrange" to insert smallest value in array separated by a word

I have a column with numbers and the word "Null" between these numbers. For a particular array, I want a code which puts in each row of that array the array's smallest number. E.g the column looks like this
Null
52
48.4
36.9
Null
Null
Null
Null
12
17
25.0
26.6
29.4
Null
Null
Null

And the result starts with "" because of the Null above followed by the smallest value of 36.9176 for the first array and then 12 for the last. The gaps represent the position of the "Null" word.

36.9
36.9
36.9




12
12
12
12
12


I have tried the codes but to no avail. Please anyone to help.

Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

Sub Smallest()
Dim rng As Range
Dim minimum As Double
Application.Volatile
Set rng = Intersect(rng.Parent.UsedRange, rng)
minimum = Application.WorksheetFunction.Min(rng)
Range = ("X9:X24")
MsgBox minimum

End Sub

OR

Function minimum(Rng As Range) As Double
    Dim Cell As Range
    Dim Cnt As Long
    Dim Mina As Double    
    Application.Volatile    
    Set Rng = Intersect(Rng.Parent.UsedRange, Rng)        
    For Each Cell In Rng
        If Cell.Value > 0 Then
            Cnt = Cnt + 1
            Mina = Cell.Value            
    Else
       If Cell.Value < Mina Then
          Mina = Cell.Value
         Else
         If Cell.Value = "Null" Then
         Mina = ""    
     End If    
       Cnt = 0
       minimum = Mina
   End If
   End If
   
Next

End Function

How about just a formula?

Row\Col	A	B	C
1
2	Null	--	B2 and down: =IF(A2="Null", "--", MIN(INDEX(A$1:A2, LOOKUP(9E+307, ROW(A$1:A2) / (A$1:A2="Null"))):INDEX(A2:A$17, MATCH("Null", A2:A$17, 0))))
3	52	36.9
4	48.4	36.9
5	36.9	36.9
6	Null	--
7	Null	--
8	Null	--
9	Null	--
10	12	12
11	17	12
12	25	12
13	26.6	12
14	29.4	12
15	Null	--
16	Null	--
17	Null	--

Thank you shg. I did not mention that the first column is made up of variables and hence a formula isn't adequate. For instance, if all the cells in column A get filled with numbers, then all the cells in B should have a single number which must be the smallest of the numbers in A since the array then would be the whole of A. The trouble is automating the response to cover whatever degree that cells in A get filled with numbers.

If Null might naot appear above or below or both, in A2 and copy down,

=IF(A2="Null", "--", MIN(INDEX(A$1:A2, IFERROR(LOOKUP(9E+307, ROW(A$1:A2) / (A$1:A2="Null")), 1)) : INDEX(A2:A$17, IFERROR(MATCH("Null", A2:A$17, 0), ROWS(A2:A$17)))))

If a UDF is what you want,

Function J(ByVal rSrch As Range) As Variant
    Dim rFrm        As Range
    Dim rInt        As Range
    Dim rUp         As Range
    Dim rDn         As Range

    Set rSrch = Intersect(rSrch, rSrch.Worksheet.UsedRange)
    Set rFrm = Application.Caller
    Set rInt = Intersect(rFrm.EntireRow, rSrch.EntireColumn)

    If LCase(rInt.Value2) = "null" Then
        J = "--"

    Else
        Set rUp = rSrch.Find(What:="Null", _
                             After:=rInt, _
                             LookIn:=xlValues, _
                             LookAt:=xlWhole, _
                             MatchCase:=False, _
                             SearchDirection:=xlPrevious)
        If rUp Is Nothing Then Set rUp = rSrch(1)
        If rUp.Row > rInt.Row Then Set rUp = rSrch(1)

        Set rDn = rSrch.Find(What:="Null", _
                             After:=rInt, _
                             SearchDirection:=xlNext)
        If rDn Is Nothing Then Set rDn = rSrch(rSrch.Cells.Count)
        If rDn.Row < rInt.Row Then Set rDn = rSrch(rSrch.Cells.Count)

        J = WorksheetFunction.Min(Range(rUp, rDn))
    End If
End Function

Can you provide an example where it doesn't work?

No?

Row\Col	A	B	C
1
2	Null	--	B2 and down: =J(A:A)
3	52	36.9
4	48.4	36.9
5	36.9	36.9
6	Null	--
7	Null	--
8	Null	--
9	Null	--
10	12	12
11	17	12
12	25	12
13	26.6	12
14	29.4	12
15	Null	--
16	Null	--
17	Null	--