Rounding with VBA

[Cricketer]

Hello

I’m updating various cell values using Special Paste and this works fine but I need to Round the result to whole numbers. Can anyone please advise how to modify the macro below which fails at Cell contents line?

Range("W6").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "M"
Range("AH2").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Copy
Range("K8:K15").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
SkipBlanks:=False, Transpose:=False
CellContents = cell.FormulaR1C1 = "= Round(" & CellContents & ", " & RoundUp & ")"
End Sub

Cricketer

 

[MichaelRo]

From what your code suggests - You want to a formula to roundup each cell value you have just pasted.

However if you use a formula in the cell you want to round up you will create several circular references

How about a loop through the cells you specified:

Dim c As Range
For Each c In Range("K8:K15")
    c.Value = Application.WorksheetFunction.RoundUp(c, 0)
Next

 

[Cricketer]

Hello Michael

That worked great

Many thanks

Cricketer