In VBA replace formula output with text value

Question

I have VBA code to make my column WEEKNUM formula say "Late" if it is in a certain range of weeks. The formula turns the week number into the word "Late" when the code is run; however, this goes to a pivot table on the sheet over and the "Late" values are not showing up when the table is refreshed since it is still in the form as a formula. Is there a way that whenever the WEEKNUM value is "Late" to clear out the cell and enter the text "Late"? Better yet can the formula output be replaced with a text value?

    With MyWorkbook.Worksheets("sheet2")
    With .Range(.Cells(2, "X"), .Cells(.Rows.Count, "O").End(xlUp).Offset(0, 9))
        .Formula = "=weeknum(o2)"
        .NumberFormat = "0_)"
        .FormatConditions.delete
        With .FormatConditions.Add(Type:=xlExpression, Formula1:="=and(x2<weeknum(today()), year(o2)=year(today()))")
            .NumberFormat = "\L\a\t\e_)"
        End With
    End With
End With

Just clarifying: you have tried manually changing the formulas to "Late" where appropriate, and this plus the pivot table refresh had the desired result? — Mistella
Yes, I have manually changed the formulas to "Late" and the pivot table reads the "Late" values in that column, making it a part of the first row header in the table. — user10003190
Would there be a way to replace the output so that "Late" is not part of a formula and just text in a cell? — user10003190

Mistella Mistella · Accepted Answer · 2018-07-11T17:52:25

This will give "Late" as the result of the formula instead of part of the formatting. Since pivot tables usually treat formula-result text as they would "just" text, this should give the desired result.

Replace:

With .Range(.Cells(2, "X"), .Cells(.Rows.Count, "O").End(xlUp).Offset(0, 9))
    .Formula = "=weeknum(o2)"
    .NumberFormat = "0_)"
    .FormatConditions.Delete
    With .FormatConditions.Add(Type:=xlExpression, Formula1:="=and(x2<weeknum(today()), year(o2)=year(today()))")
        .NumberFormat = "\L\a\t\e_)"
    End With
End With

with this:

With .Range(.Cells(2, "X"), .Cells(.Rows.Count, "O").End(xlUp).Offset(0, 9))
    .Formula = "=if(and(weeknum(o2)<weeknum(today()), year(o2)=year(today())),""Late"",weeknum(o2))"
    .NumberFormat = "0_)"
End With

In VBA replace formula output with text value

1 Answers