PDA

View Full Version : i want required vba. I am looking for a small value from the Range



sanju2323
07-28-2014, 11:00 PM
ValueRangeRequirement Lowest Value By The Range25.0001750.00017.00017.0001750.00017.00033.0001750.00017.00018.0001750 .00017.00033.0001750.00017.00047.0001750.00017.00066.0001750.00017.00021.00 01750.00017.00041.0001750.00017.00098.0003500.0005.00045.0003500.0005.00070 .0003500.0005.0005.0003500.0005.00053.0003500.0005.00055.0003500.0005.00047 .0003500.0005.00012.0003500.0005.00015.0005250.00014.00066.0005250.00014.00 014.0005250.00014.00045.0005250.00014.00068.0005250.00014.00021.0005250.000 14.00075.0005250.00014.00077.0005250.00014.00089.0005250.00014.00017.000525 0.00014.000Need VBA Codes.I am looking for a small value between Range

kevvukeka
07-28-2014, 11:23 PM
Hi sanju,

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



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

.

sanju2323
07-28-2014, 11:42 PM
Thanks, But I want result shown as VBA Formula
Ex. test(A2,B2)

xld
07-29-2014, 02:28 AM
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

sanju2323
07-29-2014, 07:20 AM
You are all the right to hang the sheet. This is no way to