Transferring formula to vba

[YasserKhalil]

Hello everyone
I have the following formula which I used to round numbers specially
for example :
20.1 and 20.2 becomes 20
20.3, 20.4, 20.5,20.6,20.7 becomes 20.5
20.8 and 20.9 becomes 21

I want to convert it to use it in VBA
The fomula:::


=IF(OR(ROUND(MOD(A1;1);1)=0.1;ROUND(MOD(A1;1);1)=0.2;ROUND(MOD(A1;1);1)=0.6 ;ROUND(MOD(A1;1);1)=0.7);FLOOR(A1;0.5);CEILING(A1;0.5))
A1 in this formula should be replaced by cel in vba...

The summary is that VBA look at the cell value and round the numbers as explained

[snb]

[vba]Sub snb()
For j = 2 To 10
x = 10*Cells(j, 1) Mod Int(Cells(j, 1))
Cells(j, 2) = IIf(x> 7 Or x = 3 Or x = 4, Application.Ceiling(Cells(j, 1), 0.5), Application.Floor(Cells(j, 1), 0.5))
Next
End Sub

[/vba]

[YasserKhalil]

Thanks for quick reply
In my original code I used the loop for each cel1 in rng ... Next

[VBA]Dim r1 As Range
Dim r2 As Range
D = Cells(Rows.Count, "A").End(xlUp).Row

Set r1 = Sheets("RST").Range("G10:M" & D)
Set r2 = Sheets("RST").Range("P10:R" & D)
Set rng = Union(r1, r2)
Application.ScreenUpdating = False
Application.Calculation = xlManual
On Error Resume Next
For Each cel1 In rng
cel1.Value = Application.WorksheetFunction.Ceiling(cel1, 0.5)
Next cel1
With Sheets("RST").Range("N10:N" & D)
.FormulaR1C1 = "=SUM(RC7:RC13)"
.Value = .Value
End With
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True[/VBA]

This line is what I need to change
[VBA]cel1.Value = Application.WorksheetFunction.Ceiling(cel1, 0.5)[/VBA]

[snb]

This line is what I need to change

I agree.
You've got all the information to do so.