VBA to run macro only if specific text shows anywhere in cell.

[Livin404]

Greetings,

The cell I have below works perfect, and I just need to run the macro ONLY if cell "K6" contains the word "Domestic" anywhere within the cell. It can before or after another word, or after a special character.

The Code below is fine, I would love just to add this one little restriction before it.

Thank you,

Sub Domestic_Operator()
   Dim i As Long, n As Variant, v As Variant
  
    For i = 6 To Split(Worksheets("MSN Decoder").UsedRange.Address, "$")(4)

        n = Mid(Worksheets("MSN Decoder").Cells(i, "K").Value, 4, 2) ''' Extracts the 4th and 5th characters

        v = Application.VLookup(n, Worksheets("Operator").Range("D:E"), 2, 0)

        If IsError(v) Then v = Application.VLookup(Val(n), Worksheets("Operator").Range("D:E"), 2, 0)

        If Not IsError(v) Then Worksheets("MSN Decoder").Cells(i + 4, "H").Value = v

    Next i

End Sub

 

[Micron]

Maybe
Dim i As Long, n As Variant, v As Variant

IF Instr(Range.("K6"),"Domestic") > 0 Then
. . For... etc.

Be warned that UsedRange detects formatting, so if you have formatted cells 10,000 rows down, that's how far your loop is going.

 

[Livin404]

Maybe
Dim i As Long, n As Variant, v As Variant

IF Instr(Range.("K6"),"Domestic") > 0 Then
. . For... etc.

Be warned that UsedRange detects formatting, so if you have formatted cells 10,000 rows down, that's how far your loop is going.

Thank you, is there an issue with the brackets? I get an bracket or expression error.

 

[JoeMo]

Remove the dot after Range --> Range("K6"), ...

 

[Micron]

@JoeMo; nice catch.
Sorry for the free-form typo.