only display positive values
 

I have a spreadsheet that forecasts based on history, unfortunately where
there have been negative sales the formula creates a negative forecast which
I want to read as zero. I am using the macro below

thanks

Dim i As Long
For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count

If Not Cells(i, "V").Locked Then _
Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3"
Cells(i, "V").Copy
If Not Cells(i, "V").Locked Then _
Cells(i, "V").PasteSpecial Paste:=xlValues

only display positive values
 

Try an IF statement in your formula:

Cells(i, "V").FormulaR1C1 = "=IF(SUM(RC15:RC17)/3<0,0,SUM(RC15:RC17)/3)"

Mike F

"Michael" wrote in message
...
I have a spreadsheet that forecasts based on history, unfortunately where
there have been negative sales the formula creates a negative forecast
which
I want to read as zero. I am using the macro below

thanks

Dim i As Long
For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count

If Not Cells(i, "V").Locked Then _
Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3"
Cells(i, "V").Copy
If Not Cells(i, "V").Locked Then _
Cells(i, "V").PasteSpecial Paste:=xlValues

only display positive values
 

Cells(i, "V").FormulaR1C1 = "=IF(SUM(RC15:RC17)<0,0,SUM(RC15:RC17)</3)"
--
- K Dales


"Michael" wrote:

I have a spreadsheet that forecasts based on history, unfortunately where
there have been negative sales the formula creates a negative forecast which
I want to read as zero. I am using the macro below

thanks

Dim i As Long
For i = 31 To Application.ActiveSheet.UsedRange.Rows.Count

If Not Cells(i, "V").Locked Then _
Cells(i, "V").FormulaR1C1 = "=SUM(RC15:RC17)/3"
Cells(i, "V").Copy
If Not Cells(i, "V").Locked Then _
Cells(i, "V").PasteSpecial Paste:=xlValues