当我在 VBA 中使用命名范围时,我收到错误"该命令不能用于多项选择"。
VBA 用于派生一个很好,但我想只粘贴值。
Set rngCopy = rngCopy.Offset(0, 10).SpecialCells(xlCellTypeVisible)
rngCopy.Activate
rngCopy.Value = _
"=IF(RC[-10]="""","""",IF(WEEKDAY(RC[-10])=2,RC[-10]-3,IF(WEEKDAY(RC[-10])<>2,RC[-10]-1)))"
rngCopy.Copy
rngCopy.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
它对我有用。此外,如果您打算将公式复制粘贴为值,那么您不需要采取那么长的路线。您可以简单地使用Application.Evaluate
Sub Sample()
Dim rngCopy As Range
Set rngCopy = Range("A1")
Set rngCopy = rngCopy.Offset(0, 10).SpecialCells(xlCellTypeVisible)
rngCopy.Value = Application.Evaluate( _
"=IF(RC[-10]="""","""",IF(WEEKDAY(RC[-10])=2,RC[-10]-3,IF(WEEKDAY(RC[-10])<>2,RC[-10]-1)))" _
)
End Sub
使用非连续范围进行测试也有效。
Sub Sample()
Dim rngCopy As Range
Set rngCopy = Union(Range("A1"), Range("D1"), Range("F1"))
rngCopy.Activate
rngCopy.Value = Application.Evaluate( _
"=IF(RC[-10]="""","""",IF(WEEKDAY(RC[-10])=2,RC[-10]-3,IF(WEEKDAY(RC[-10])<>2,RC[-10]-1)))" _
)
End Sub