Min function with a variable range

[Bill Roberts]

I have 3 columns and 100 rows of data. In column C, I want to calculate the
difference between the value in column B less the minimum value in the
previous 10 rows of column B. Following are a bunch of codes that do not
work. Please suggest one that does. TIA
For rowno = 10 To 100
Cells(rowno,â€Câ€).select
Cells(rowno, "C").Value = (rowno, "B").Value -
Application.WorksheetFunction.Min(Range("B2:B10")
(This works but it doesn’t move the range down for the next row).
Cells(rowno, "C").Value = Cells(rowno, "B").Value - ActiveCell.FormulaR1C1 =
"=MIN(R[-9]C[-1]:RC[-1])"
Cells(rowno, "C").Value = Cells(rowno, "B").Value -
Application.WorksheetFunction.Min(Range("C,rowno-10:C,rowno"))
Next

 

[Don Guillett]

Sub runningminSAS()
mc = 2 'column B
For i = 10 To Cells(Rows.Count, mc).End(xlUp).Row
Cells(i, mc + 2).Value = Cells(i, mc) - _
Application.Min(Cells(i - 9, mc).Resize(9))
Next i
End Sub

 

[Bill Roberts]

Works just great. I need to learn more about the resize function. Thanks
--
Bill Roberts

Sub runningminSAS()
mc = 2 'column B
For i = 10 To Cells(Rows.Count, mc).End(xlUp).Row
Cells(i, mc + 2).Value = Cells(i, mc) - _
Application.Min(Cells(i - 9, mc).Resize(9))
Next i
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have 3 columns and 100 rows of data. In column C, I want to calculate the
difference between the value in column B less the minimum value in the
previous 10 rows of column B. Following are a bunch of codes that do not
work. Please suggest one that does. TIA
For rowno = 10 To 100
Cells(rowno,â€ÂCâ€Â).select
Cells(rowno, "C").Value = (rowno, "B").Value -
Application.WorksheetFunction.Min(Range("B2:B10")
(This works but it doesn’t move the range down for the next row).
Cells(rowno, "C").Value = Cells(rowno, "B").Value - ActiveCell.FormulaR1C1
=
"=MIN(R[-9]C[-1]:RC[-1])"
Cells(rowno, "C").Value = Cells(rowno, "B").Value -
Application.WorksheetFunction.Min(Range("C,rowno-10:C,rowno"))
Next

.