VBA - rounding amounts (multiple cols) AND 'ignore text'

[dmj120]

Sooo.....this is a follow-on to a previous post. I'm trying to modify the below to:

• Round across H4:O, instead of Range("I2:I6000")
• Ignore any errors - the below give a #value error if text is entered, which is fine (in that workbook, lol)

I tried to simply change the range to Set rng = Intersect(Target, Range("H4:O")) which errors out.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrTrap

    Dim rng As Range

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

    If Not rng Is Nothing Then

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



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

    End If

Exit Sub

 

[rlv01]

Round across H4:O, instead of Range("I2:I6000")

I2:I6000 is a range of cells. H4:O is not. You need an ending row number to make it a range, e.g. H4:O5000

 

[dmj120]

Changing to Set rng = Intersect(Target, Range("H4:O70001")) gives a compile error.

I2:I6000 is a range of cells. H4:O is not. You need an ending row number to make it a range, e.g. H4:O5000

 

[rlv01]

Changing to Set rng = Intersect(Target, Range("H4:O70001")) gives a compile error.

View attachment 93068

The change had nothing to do with it. It would have given you that error if you had left the original range in place. Each subroutine must end with the statement End Sub and yours does not.

 

[bebo021999]

I think this is enough

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'Do nothing if range does not change OR text value OR cell is deleted
If Intersect(Target, Range("H2:O6000")) Is Nothing Or _
    Not IsNumeric(Target) Or IsEmpty(Target) Then Exit Sub

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

 

[dmj120]

This works perfectly. Thank you!!

I think this is enough