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 :

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

[Luckyplexi]

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

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

You can also use specialcells() to find errors

Please Login or Register  to view this content.

[Rick Rothstein]

You can also use specialcells() to find errors

Please Login or Register  to view this content.

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.