Change #VALUE! fields in a column

StudentOfData · Dec 23rd 2020

Column A has about 1,600 rows of data. The fields are either a number (such as 45355) or #VALUE!

I'd like to create a macro that changes every #VALUE! field to the nearest number above.

For example:

45355

#VALUE!

12345

#VALUE!

Should become:

45355

12345

Can anyone help me with how I would create a macro to do this please?

dangelor · Dec 24th 2020

Possibly...

Code

Sub test()
    Dim v As Variant, i As Long
    
    'Change workbook and worksheet as needed
    With ThisWorkbook.Worksheets("Sheet1").UsedRange
        v = .Columns(1)
        For i = LBound(v) To UBound(v)
            If IsError(v(i, 1)) Then v(i, 1) = v(i - 1, 1)
        Next i
        .Columns(1) = v
    End With
End Sub

Display More

StudentOfData · Dec 24th 2020

That worked perfectly. Thank you very much!

dangelor · Dec 24th 2020

Glad to hear! You're welcome!

Change #VALUE! fields in a column

Participate now!

Share