VBA Macro for Removing #N/As

[Luckyplexi]

Hi all,

I just registered to the forum so nice to meet you all. Before we start, a little bit of my background so you can know who you're helping. I'm a 24yo french finance master's degree student. I'm currently doing my master's thesis whose subject is mutual funds' performance and fees. I basically want to know if an investor who pays more fees will receive more performance, as fund managers claim fees such as management fees are the reflect of their skills.
So to be able to show whether fees and performance are positively correlated or not, I downloaded a massive database with 10k funds and their monthly NAV (or price) starting from January 2004 (so roughly 200 observations/fund). Now of course, I have lots of #N/A in my database and for 2 reasons :
- market was closed that day
- or the fund was not alive yet
I would like to clean that database and get rid off these #N/As. To do so, I want to target each #N/As that are comprised between 2 values and use the average of these 2 values to replace the #N/A.

I just wrote a VBA code to help me do that quickly but it doesn't work :

Public Sub RemoveNA()

Dim row_number As Long
Dim col_number As Long
Dim line_number As Long
Dim column_number As Long

column_number = InputBox("How many columns?")
line_number = InputBox("How many rows?")

For col_number = 1 To column_number
    For row_number = 1 To line_number
    If Cells(row_number, col_number).Value = "#N/A" Then
        Cells(row_number, col_number).Value = 0
    End If
    Next row_number
Next col_number

End Sub

Here is a snapshot of what my excel look like :

LP61500731 LP61501237 LP61500989 LP61501059 LP40209239 LP40209256
Net Asset Value 31/08/2020 51.44 30.01 18.30 13.43 11.78 14.13
Net Asset Value 31/07/2020 52.48 30.65 18.63 13.71 10.94 13.45
Net Asset Value 30/06/2020 52.77 30.75 18.70 13.64 10.42 12.84
Net Asset Value 29/05/2020 52.96 30.79 18.74 13.39 10.06 12.65
Net Asset Value 30/04/2020 51.78 29.99 #N/A 12.95 9.57 12.07
Net Asset Value 31/03/2020 44.53 25.96 15.74 11.67 8.52 11.13
Net Asset Value 28/02/2020 52.21 30.00 18.38 13.18 #N/A 12.60
Net Asset Value 31/01/2020 58.71 33.51 20.60 14.06 12.48 13.68
Net Asset Value 30/12/2019 61.57 34.98 21.52 15.20 13.07 13.63

Hope someone understands my problem and can give me any clue about how to solve it

Have a wonderful day,

Alexis

[davesexcel]

Try the replace function

Worksheets("Sheet1").Columns("A:Z").Replace What:="#N/A", Replacement:=0, SearchOrder:=xlByColumns, MatchCase:=True

[Luckyplexi]

Just noticed I copy paste the wrong code. Here is what I meant :

Public Sub RemoveNA()

Dim row_number As Long
Dim col_number As Long
Dim line_number As Long
Dim column_number As Long

column_number = InputBox("How many columns?")
line_number = InputBox("How many rows?")

For col_number = 1 To column_number
    For row_number = 1 To line_number
    If Cells(row_number, col_number).Value = "#N/A" Then
        Cells(row_number, col_number).Value = Application.WorksheetFunction.Average(Cells(row_number - 1, col_number).Value, Cells(row_number + 1, col_number).Value)
    End If
    Next row_number
Next col_number

End Sub

True to say the replacement function is particularly efficient at spotting the #N/A but what I would like to do is replace each one by the average of the above and the below values.

I thought my code would work but it doesn't..

Best

[davesexcel]

Try using text instead.
You can reduce the loop as well, to start at row3 and column 3

Sub RemoveNA()
    
    Dim row_number  As Long
    Dim col_number  As Long
    Dim line_number As Long
    Dim column_number As Long
    
    column_number = InputBox("How many columns?")
    line_number = InputBox("How many rows?")
    
    For col_number = 3 To column_number
        For row_number = 3 To line_number
            If Cells(row_number, col_number).Text = "#N/A" Then
                Cells(row_number, col_number).Value = Application.WorksheetFunction.Average(Cells(row_number - 1, col_number).Value, Cells(row_number + 1, col_number).Value)
            End If
        Next row_number
    Next col_number
    
End Sub

You can also use specialcells() to find errors

Sub ErrorRplce()
Dim rng As Range

Set rng = Columns("C:Z").SpecialCells(xlCellTypeConstants, 16)
rng = "=AVERAGE(R[-1]C,R[1]C)"
rng.Value = rng.Value
End Sub

[Rick Rothstein]

You can also use specialcells() to find errors

Sub ErrorRplce()
Dim rng As Range

Set rng = Columns("C:Z").SpecialCells(xlCellTypeConstants, 16)
rng = "=AVERAGE(R[-1]C,R[1]C)"
rng.Value = rng.Value
End Sub

Usually #N/A errors are generated by formulas, so you might want to suggest xlFormulas in place of the xlCellTypeConstants in you code. As a side note, the xlCellTypeConstants argument can also be written xlConstants and it will work also.

[davesexcel]

I was surprised it worked as well when I tested it.
I copied the OP's data and it worked as expected, that is why I shared it.