Excel VBA Run-time error '424' Object required replacing cell contents with rounded to 2 decimals of current values

Question

I am trying to replace the contents of certain cells with the rounded of the same amounts currently there. The error is on the line where I define the LastRow variable (within the With/End With command). The weird thing is, this code used to work fine a few weeks ago.

Here is my code:

    Dim LastRow As Integer

    '   Looks up the last row of data and assigns it to one of the variables on columns I and J
    With ActiveWorksheet
        LastRow = .Range("I" & .Rows.Count).End(xlUp).Row
    End With

    Set rng = Range("I2:J" & LastRow)

    '   Replaces current contents of columns I and J with the same value but rounded to 2 decimals
    For Each cell In rng
        If cell = "" Then Exit Sub
        cell.Value = WorksheetFunction.Round(cell.Value, 2)
    Next cell

Also: Qualify the `Range` object with your `ActiveSheet` too when you set `rng` — braX, Jan 02 '20 at 17:42

score 2 · Accepted Answer · answered Jan 02 '20 at 16:51

2

There is no such thing as ActiveWorksheet, only ActiveSheet.

Change ActiveWorksheet to ActiveSheet (and add Option Explicit to the top of the module).

_{Also note that you should use Long instead of Integer to avoid a potential Overflow error.}

answered Jan 02 '20 at 16:51

BigBen

46,229
7
24
40

More on the potential overflow error here: https://stackoverflow.com/questions/31436397/vba-integer-overflow-at-70-000/31436414 – Instant Breakfast Jan 02 '20 at 17:27

Excel VBA Run-time error '424' Object required replacing cell contents with rounded to 2 decimals of current values

1 Answers1