Cut and paste value from fixed cell to a variable cell

[LalitPandey87]

Change below line of code

Const strSearchCriteria As String = "A"

with this one

Const strSearchCriteria As String = "Drinks"

[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

[Charles_]

Thanks LalitPandey87!

I am getting Run - time error "Object variable or With block variable not set. It is highlighting the below line in bold.
I know I have to set the variables on that line but not sure where to.

Thanks again for your help!!


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

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

[LalitPandey87]

But i didn't find any error. Working on my system.