Rounding in excel using VBA

[pjmsimmons]

Hi All,
I am trying to get excel to calculate a value and round it to 2 decimal places before entering into a separate worksheet. I have all of it working bar the rounding part. I cant seem to figure out how to get VBA to round the value to 2 decimal places before entering into the worksheet. I have posted the code below. The bit of interest is in the section labelled 'Update the prepayment file.

I want the calculation 'Y.Offset(0, 9) / 3.9' to be entered into '.Cells(lRow + 1, "B")' rounded to 2 decimal places.

Can anyone help?

regards,

paul

Sub UpdatePrepaymentFile_Cheque_Payments()
    Dim CompareRange1 As Range, CompareRange2
    Dim x As Range, Y As Range
    Dim sTargetSheet As String, operatorname As String
    Dim lRow As Long, dtStamp As Date
    
    Application.ScreenUpdating = False
    operatorname = InputBox("Enter your initials")
    
    ' Identify list of unique herd codes and date tested
    With Workbooks("Prepaid account cheque payments.xlsm")
        With .Worksheets("Sheet1")
            Set CompareRange2 = .Range("C1:C" & _
                .Cells(.Rows.Count, "C").End(xlUp).Row)
        End With
        dtStamp = Date
    End With
    
    ' Compare the two lists and label the prepaid tested herd codes in the daily record plate layout workbook
    With Workbooks("Prepayment File.xlsm")
        With .Worksheets("Summary")
            Set CompareRange1 = .Range("A2:A" & _
                .Cells(.Rows.Count, "A").End(xlUp).Row)
        End With
        For Each x In CompareRange1
            For Each Y In CompareRange2
                If x = Y And x <> vbNullString Then
                    
                
                    ' Update prepaid herd code account balances in the prepayment file
                    sTargetSheet = x.Offset(0, 2)
                    With .Sheets(sTargetSheet)
                        lRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                        If lRow < 16 Then lRow = 16
                        
                        ' Update the prepayment file
    If Y.Offset(0, 6) = "" Then .Cells(lRow + 1, "B") = Y.Offset(0, 9) / 3.9
    If Y.Offset(0, 6) <> "" Then .Cells(lRow + 1, "B") = Y.Offset(0, 9) / 3.6
   
                        .Cells(lRow + 1, "A") = dtStamp
                        .Cells(lRow + 1, "H") = operatorname
                    End With
                End If
            Next Y
        Next x
    End With
End Sub

 

[shg]

... Then .Cells(lRow + 1, "B").Value = Round(Y.Offset(0, 9).Value / 3.9, 2)

 

[pjmsimmons]

Thanks,SHG. Your help is much appreciated

Regards

Paul

 

[shg]

You're welcome, good luck.