VBA rounding with formula

dmj120

Active Member
Joined
Jan 5, 2010
Messages
286
Office Version
  1. 365
  2. 2019
  3. 2010
I'm trying to use a snippet of code from a previous post. But when I add this to the sheet, nothing happens. Where's my mistake?

All I'm trying to do is in column I, the amount entered uses the formula = Round(cell.Value / 365, 2) * 365 to calculate when I enter a value.

VBA Code:
Option Explicit

Dim rng As Range
Dim cell As Range
   
Set rng = Intersect(Target, Range("I2:I15000"))
   
If Not rng Is Nothing Then

    Application.EnableEvents = False
   
    For Each cell In rng
        If IsNumeric(cell.Value) Then
            cell.Value = Round(cell.Value / 365, 2) * 365
        End If
    Next cell
   
    Application.EnableEvents = True
    
End If

1670358808317.png
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.
Hi there,

I take it you only want to change the cell that's just been changed not the entire range each time?
 
Upvote 0
Try this event macro on the sheet in question:

VBA Code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrTrap

    Dim rng As Range

    Set rng = Intersect(Target, Range("I2:I15000"))

    If Not rng Is Nothing Then

        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With
       
        Target.Value = Evaluate("ROUND(" & Target.Address & "/365,2)*365")

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With

    End If

Exit Sub

ErrTrap:

    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Regards,

Robert
 
Upvote 0
Try this event macro on the sheet in question:

Regards,

Robert

Thanks, yes that works. If you delete the value, $0 is entered. Is there a way to have the cell 'blank' if you delete the value?
 
Upvote 0
Is there a way to have the cell 'blank' if you delete the value?

Yes - add this:

VBA Code:
If Len(Target) > 0 Then
    Target.Value = Evaluate("ROUND(" & Target.Address & "/365,2)*365")
End If
 
Upvote 0
Solution
Add a line to clear the cell.
VBA Code:
Target.Value = Evaluate("ROUND(" & Target.Address & "/365,2)*365")
If Target.Value =0 then Target.ClearContents
 
Upvote 0
Add a line to clear the cell.
VBA Code:
Target.Value = Evaluate("ROUND(" & Target.Address & "/365,2)*365")
If Target.Value =0 then Target.ClearContents

That will clear the cell if the result of the formula equals zero. I could be wrong but I don't think that's what the OP is asking for.
 
Upvote 0
That will clear the cell if the result of the formula equals zero. I could be wrong but I don't think that's what the OP is asking for.

I was hoping to clear the cell. Thanks for your replies!
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top