VBA replacing a formula with a macro returns #Value (Wrong data type)

I have an excel macro that copies a tab from the previous month, renames it to the correct month, and replaces the formulas with the correct month/formula. The issue I ran into is that when it replaces the formula with the correct month, it returns #Value and says "wrong data type".

I believe that the issue is that I am replacing a formula that has underlying data with a text string. The replacement macro is below:

Please Login or Register  to view this content.

The formula that this replaces is:

Please Login or Register  to view this content.

Does anyone know how to use VBA to replace text in a formula as well as the underlying data?

What is the value of all the variables at the time that code runs (iYear, lmonth, nmonth, nYear) ?
Can you post the code that gives those variables their values?

What is the before AND after of the formula?
What it looks like before, and what you expect it to be changed to by the macro

iYear = 2013, lmonth = Mar, nmonth = Apr, nYear = 2013 unless lmonth is Dec, then it is 2014
Basically, it replaces "Mar 2013" with "Apr 2013" in the formula
It does replace the text in the formula but the cell says it is wrong data type.

The macro replaces this:
=COUNTIF( 'Mar 2013 details'!E2:E299,"support")

With this:
=COUNTIF( 'Apr 2013 details'!E2:E299,"support")

Given that code, and the values of the variables you just posted..

I can only produce the Value Error from the formula IF there is no sheet named "Apr 2013 details" in the book

The application.displayalerts = false is suppressing the popup box you get to browse to a valid book/sheet reference.
Forcing the formula to be entered with an invalid sheet reference.

Try commenting out that line

It may be easier if I attach the document so you can see exactly what I am talking about. I put the "application.displayalerts = false" in because it would ask me to select the reference manually, and I was unable to troubleshoot with all the errors popping up (1 error for each find/replace for a total of 9 pop-ups). I did remove that line, just to test, and got the samee results.

I have attached the spreadsheet for your reference. If you hit "ctrl + n" with macros enabled it should create the next two pages with the #Value error on the first one.

Originally Posted by silent3486

I put the "application.displayalerts = false" in because it would ask me to select the reference manually.

That confirms what I suggested in previous post...
There is no sheet named "Apr 2013 details" <- this is why it prompts you to select the reference manually.
And in fact there is not a sheet by that name in the book you just posted.

That was the fix! I moved the find/replace code to after the Apr 2013 details was created and it works perfectly. Thank you Jonmo

Great, glad it worked out.