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 formula is below:


    Code
    ' 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:


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


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

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


    [INDENT][INDENT]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")[/INDENT]
    [/INDENT]

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


    [INDENT]I see that it definitely works in your file. It may be easier if I attach the document so you can see exactly what I am talking about. 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. [/INDENT]

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


    You need to move the section in the macro that creates the "Apr 2013 details" sheet before the section that creates the "Apr 2013" sheet because you create the "Apr 2013" sheet and change the formulas to refer to a sheet that doesn't exist yet, then when you create that sheet it's too late and the formulas won't correct themselves. If you create the "details" sheet first, then it works.

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


    Thank you Brian! That worked perfectly. Problem is solved :)


    One more question. Is there a way to give you kudos and mark this thread as solved?

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login