CODEWITHSUNDEEP
excelvba
theredindian
theredindian

Reputation: 27

Convert formulas to values if condition is met

I want to convert formulas to values in col P (ReportedGrossActivityReduction), for rows where col W (test) is Yes.

I created the following macro by recording and amended after searching online.

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

Upvotes: 1

Views: 7888

Answers (2)

DisplayName
DisplayName

Reputation: 13386

here is a "no-loop" solution to leave formula result only in column P cells whose corresponding column W cells content is "Yes":

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

Upvotes: 1

Michał Turczyn
Michał Turczyn

Reputation: 37430

Try this (comments in code):

'hugely recommended to use this option
Option Explicit
'sub/macro which copies data over columns
Sub CopyYesInW()
    Dim lastRow As Long, i As Long
    'determine last row in column W
    lastRow = Cells(Rows.Count, "W").End(xlUp).Row
    For i = 1 To lastRow
        'if Yes in W then copy from P to W in current row
        If Cells(i, "W").Value = "Yes" Then
            Cells(i, "W").Value = Cells(i, "P").Value
        End If
    Next
End Sub

Upvotes: 0

Related Questions