Selecting Range for a Loop and Changing N/A Values to Latest Numeric Value

Question

I use this code in an attempt to clean potential N/A values in one of the columns.

Sub FixData()
Dim r As Range

Range("AL7").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select

For Each r In Selection
    If r.Text = "#N/A" Then
        r.Value = r.Offset(-1, 0).Value
    End If
Next

End Sub

The data that I want to clean is in the area AL7:AS191. The area is marked, but it doesn't seem like the loop is running. The N/A values are still there.

I have tried to do as the answer by Gary's Student suggest here: Changing #N/A values in Excel to last non-error value in the spreadsheet

Does it need minor adjustments?

All the best, Christoffer

Have you tried `For Each r In range("AL7:AS191")`? – SJR Apr 22 '20 at 10:32 — SJR, Apr 22 '20 at 10:32

score 1 · Answer 1 · answered Apr 22 '20 at 10:55

This might help. It uses the native ISNA() function to test for an #N/A value.

Sub FixData()
    Dim cl As Range, rng As Range

    Set rng = Range("AL7:AS191")

    For Each cl In rng
        If Application.WorksheetFunction.IsNA(cl) Then
            cl = cl.Offset(-1, 0).Value
        End If
    Next
End Sub

This assumes a fixed range i.e. Range("AL7:AS191").

You can dynamically size that range by using your original code (with xlDown etc).

A final point: you can use Range("AL7").CurrentRegion to select the entire block of cells (so long as they are a non-contiguous block).

Samuel Everson · Accepted Answer · 2020-04-22T10:46:51.907

Rather than using Select (see How to avoid using select in Excel VBA), declare what range or cell you are working with.

Something like...

Dim myRange As Range
Dim LastRow as Long
Dim LastColumn As Long


With ThisWorkbook.Sheets("Sheet1")
    LastRow = .Cells(.Rows.Count, 38).End(xlUp).Row
    LastColumn = .Cells(7, .Columns.Count).End(xlToLeft).Column
    Set myRange = .Range("AL7", .Cells(LastRow,LastColumn))
End with

Dim myCell as Variant

For Each myCell In myRange
    If myCell.Text = "#N/A" Then
        myCell.Value = myCell.Offset(-1, 0).Value
    End If
Next

End Sub

Of course, this set's myRange rather dynamically, if the columns to check through are always the same, you can set the range like so:

With ThisWorkbook.Sheets("Sheet1")
    LastRow = .Cells(.Rows.Count, 38).End(xlUp).Row
    Set myRange = .Range("AL7:AS" & LastRow)
End With

Selecting Range for a Loop and Changing N/A Values to Latest Numeric Value

2 Answers2