Cut and paste value from fixed cell to a variable cell

[LalitPandey87]

You can do it by using formula also

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "A"
    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.Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub

[Charles_]

Thank you LalitPandey87,

Could you please mention where should I be inserting the "Drinks" variable in the code below? I dont see where am I telling VBA to copy cell A1 to column F only if B is Drinks.

Sorry, I am quite new to VBA

Thanks!

You can do it by using formula also

Sub Test2()

    Dim strFormula                          As String
    Dim rngRange                            As Range
    
    Const strSearchCriteria                 As String = "A"
    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.Address(0, 0) & ","""")"
        rngRange.Offset(, intOutputColumnNo - 1).Value = Evaluate(strFormula)
    End With

End Sub