Filter column and print specific value to the next column in macro

[manoj_arsul]

Hi ,
Please help me to create program in Macro for below condition

1) filter column C for 3 names "Available " , "Not available " & "Expired".
2) Print Text "Its Available" in G Column next to "Available"
3) Print text "Its Not Available" in G Column next to "Not Available"
4) Print text "The Product is Expired" in G Column next to "Expired"
-

 

[alansidman]

In G1 =IF(C1="Available","It's Available",IF(C1="Not Available","It's Not Available","The Product is Expired")) and copy down.

 

[JackDanIce]

One macro suggestion:

Sub M1()

    Dim arr As Variant
    Dim x   As Long
    
    arr = Cells(1, 3).Resize(Cells(Rows.Count, 3).End(xlUp).Row).Value
    
    For x = LBound(arr, 1) To UBound(arr, 1)
        If InStr(arr(x, 1), "Available") Then
            arr(x, 1) = "Its " & arr(x, 1)
        ElseIf InStr(arr(x, 1), "Expired") Then
            arr(x, 1) = "The Product is " & arr(x, 1)
        Else
            arr(x, 1) = vbNullString
        End If
    Next x
    
    Cells(1, 7).Resize(UBound(arr, 1), 1).Value = arr
    Erase arr
    
End Sub