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

[silent3486]

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:

<!-- BEGIN TEMPLATE: bbcode_code -->

 ' This replaces all formulas with the correct monthApplication.DisplayAlerts = False Worksheets(nmonth + " " + iYear).Cells.Replace What:=lmonth + " " + iYear, Replacement:=nmonth + " " + nYear, LookAt:=xlPart, _ SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=True, _ ReplaceFormat:=False Application.DisplayAlerts = True

<!-- END TEMPLATE: bbcode_code -->The formula that this replaces is:

<!-- BEGIN TEMPLATE: bbcode_code -->

=COUNTIF( 'Mar 2013 details'!E2:E299,"support")

<!-- END TEMPLATE: bbcode_code -->Does anyone know how to use VBA to replace text in a formula as well as the underlying data?​

 

[silent3486]

Also, 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")​

 

[silent3486]

This was resolved in another forum by Jonmo1. I had to move the code that replaced "Mar 2013" with "Apr 2013" after the code that created the "Apr 2013 details" page. Basically the formula was calculating before the other page was created.

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