only display positive values

[Michael]

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

[Mike Fogleman]

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" <Michael@discussions.microsoft.com> wrote in message
news:5EBAFFAD-3EC1-462B-8D3B-77C16E869DD4@microsoft.com...
>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

[K Dales]

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