(Solved) – Excel Macro VBA – Does cell contain instead of exact match

  • by
(solved)-–-excel-macro-vba-–-does-cell-contain-instead-of-exact-match

I am new to coding in general and need to write a macro. The below code searches for text in a field and if so it takes a quantity from that column and duplicates it in another cell. Then repeats for the entire Row, Which does work, but the text in the cell needs to be exact.

Sub Test()

Dim variable As String
variable = "insert value or cell here"

With Sheets("Test")
    LR = .Cells(Rows.Count, "N").End(xlUp).Row
    For i = LR To 2 Step -1
        If .Cells(i, "N").Value = "Blue" Or .Cells(i, "N").Value = "Red" Or .Cells(i, "N").Value = "Green" Then
            .Cells(i, "AV").Value = .Cells(i, "P").Value
        End If
    Next i
End With

End Sub

With some looking around online I have put together the below code to instead come back positive if the value appears anywhere in that cell, but I cant get it to work. Any help is appreciated.

Sub Test()

Dim variable As String
variable = "insert value or cell here"

With Sheets("Test")
    LR = .Cells(Rows.Count, "M").End(xlUp).Row
    For i = LR To 2 Step -1
        If InStr(.Cells(i, "M"), Value = "Red" or Value = "Green" or Value = "Blue") Then
            .Cells(i, "AV").Value = .Cells(i, "P").Value

        Else: .Cells(i, "AV").Value = "0"
        End If
    Next i
End With

End Sub

Leave a Reply

Your email address will not be published. Required fields are marked *