时间戳

首页 > 解决方案 > 如果满足条件，则将公式转换为值

excel - 如果满足条件，则将公式转换为值

问题描述

我想将公式转换为 col P (ReportedGrossActivityReduction) 中的值，用于 col W (test) 为 Yes 的行。

我通过录制创建了以下宏，并在网上搜索后修改。

Sub q()
    Dim a As Range
    Dim b As Range
    Dim c As Range

    Set a = Range("W2").Value
    Set b = Range("P2").Value

    With ActiveSheet
        For Each b In .Range("P2")
            If a.Range("W2").Value = "Yes" Then
                Selection.Copy
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                  :=False, Transpose:=False
            End If
        Next
    End With 
End Sub

标签： excelvba

解决方案

这是一个“无循环”解决方案，仅将公式结果保留在对应列 W 单元格内容为“是”的 P 列单元格中：

Sub Main()
    Dim vals As Variant 'declare a Variant where to store an array
    With Range("W2", Cells(Rows.Count, "W").End(xlUp)) ' reference column W range from row 2 down to last not empty one
        vals = .Value ' store referenced range values
        .Replace what:="Yes", replacement:=1, lookat:=xlWhole ' replace referenced range "Yes" content with a numeric one (1) to exploit subsequent SpecialCells method usage
        With .SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, -7) 'reference column P cells corresponding to referenced range cells with numeric content 
            .Value = .Value ' leave formula result only 
        End With
        .Value = vals ' write back original referenced range values
    End With
End Sub

推荐阅读