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 formula is below:
' This replaces all formulas with the correct month Application.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
The formula that this replaces is:
Does anyone know how to use VBA to replace text in a formula as well as the underlying data?