gpt4 book ai didi

excel - 在 VBA 中,用文本值替换公式输出

转载 作者:行者123 更新时间:2023-12-04 20:30:47 30 4
gpt4 key购买 nike

我有 VBA 代码让我的列 WEEKNUM 公式说“迟到”,如果它在某个周数范围内。该公式在代码运行时将周数变成单词“Late”;但是,这会转到工作表上的数据透视表,并且在刷新表时不会显示“延迟”值,因为它仍然是公式的形式。有没有办法在 WEEKNUM 值为“Late”时清除单元格并输入文本“Late”?更好的是,公式输出可以替换为文本值吗?

    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

最佳答案

这将给出“迟到”作为公式的结果,而不是格式的一部分。由于数据透视表通常将公式结果文本视为“只是”文本,因此这应该会产生所需的结果。

代替:

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 .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

关于excel - 在 VBA 中,用文本值替换公式输出,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/51289831/

30 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com