silent3486
New Member
- Joined
- May 14, 2013
- Messages
- 3
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 -->
<!-- END TEMPLATE: bbcode_code -->The formula that this replaces is:
<!-- BEGIN TEMPLATE: bbcode_code -->
<!-- END TEMPLATE: bbcode_code -->Does anyone know how to use VBA to replace text in a formula as well as the underlying data?
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 -->
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 -->
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?