i want required vba. I am looking for a small value from the Range

Value	Range	Requirement Lowest Value By The Range
25.000	1750.000	17.000
17.000	1750.000	17.000
33.000	1750.000	17.000
18.000	1750.000	17.000
33.000	1750.000	17.000
47.000	1750.000	17.000
66.000	1750.000	17.000
21.000	1750.000	17.000
41.000	1750.000	17.000
98.000	3500.000	5.000
45.000	3500.000	5.000
70.000	3500.000	5.000
5.000	3500.000	5.000
53.000	3500.000	5.000
55.000	3500.000	5.000
47.000	3500.000	5.000
12.000	3500.000	5.000
15.000	5250.000	14.000
66.000	5250.000	14.000
14.000	5250.000	14.000
45.000	5250.000	14.000
68.000	5250.000	14.000
21.000	5250.000	14.000
75.000	5250.000	14.000
77.000	5250.000	14.000
89.000	5250.000	14.000
17.000	5250.000	14.000

Need VBA Codes.I am looking for a small value between Range

Hi sanju,

Try this.. This is not entirely mine. An expert from this forum helped me out with few weeks back. Hope this helps

Code:

---

Sub test()
Dim i As Long, strtrw As Long, endrw As Long
i = 2
strtrw = i
endrw = i
 Do Until Cells(i, 1) = ""
        If Cells(i, 2) = Cells(i + 1, 2) Then
          endrw = i + 1
         
        Else
          Range(Cells(strtrw, 4), Cells(endrw, 4)) = Application.WorksheetFunction.Min(Range(Cells(strtrw, 1), Cells(endrw, 1)))
          strtrw = i + 1
          endrw = i + 1
          End If
          i = i + 1
 Loop
 
End Sub

---

.

Thanks, But I want result shown as VBA Formula
Ex. test(A2,B2)

Code:

---

Public Sub GetMin()
Const FORMULA_MIN = "=MIN(IF($B$2:$B$<lastrow>=$B2,$A$2:$A$<lastrow>))"
Dim lastrow As Long

    With ActiveSheet
   
        lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("C2").FormulaArray = Replace(FORMULA_MIN, "<lastrow>", lastrow)
        .Range("C2").AutoFill .Range("C2").Resize(lastrow - 1)
    End With
End Sub

---

You are all the right to hang the sheet. This is no way to