replace string and add value using vlookup

[isabella]

Hi

1. I want to replace supplier and customer name to their full name if their name exist in vlookup range.
2. At the same time, I want to add Activities according to these condition:

If the supplier or customer name appear on vlookup range, the Activities are system trading.
Else, Activities are trading.

See attachment for better understanding.

[JBeaucaire]

Like so:

Option Explicit

Private Sub StandardizeName_Click()
Dim MyCell      As Range
Dim lkFIND      As Range
Dim lkRNG       As Range:   Set lkRNG = Sheets("Plants").Range("B:B").SpecialCells(xlConstants)
Application.ScreenUpdating = False

On Error Resume Next
    For Each MyCell In Worksheets("SVC").Range("F:F,H:H").SpecialCells(xlConstants)
        With MyCell
            If .Value <> "" Then
                Set lkFIND = lkRNG.Find(Left(.Value, 10), LookIn:=xlValues, LookAt:=xlPart)
                If Not lkFIND Is Nothing Then
                    MyCell.Value = lkFIND.Offset(, 1).Value
                    Set lkFIND = Nothing
                End If
            End If
        End With
        Range("A" & MyCell.Row).FormulaR1C1 = _
            "=IF(COUNTIF(Plants!C2:C3,RC8)+COUNTIF(Plants!C2:C3,RC6)>0, ""system trading"", ""trading"")"
    Next MyCell
        
Application.ScreenUpdating = True
End Sub

[isabella]

Thanks.

It is totally different with my code. I don't understand on this formula. What does it do actually?

Range("A" & MyCell.Row).FormulaR1C1 = _
            "=IF(COUNTIF(Plants!C2:C3,RC8)+COUNTIF(Plants!C2:C3,RC6)>0, ""system trading"", ""trading"")"

[JBeaucaire]

After running the macro, look at results in column A. Since worksheet formulas can do this quite easily, the macro is simply putting the formula into column A for you.