Cut and paste value from fixed cell to a variable cell

[LalitPandey87]

As per your attached Screenshots following will work:

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "Drinks"
    Const strDataStartCell                  As String = "A1"
    Const intColNoToFilter                  As Integer = 2
    Const intOutputColumnNo                 As Integer = 3
    
    With ThisWorkbook.Worksheets("Sheet1")
        Set rngRange = .Range(strDataStartCell).Resize(.Cells(.Rows.Count, 1).End(xlUp).Row, 1)
        Set rngRange = Intersect(rngRange, rngRange.Offset(1))
        strFormula = "=IF(" & rngRange.Offset(, 1).Address(0, 0) & "=""" & strSearchCriteria & """," & rngRange.Offset(, 1).Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub