VBA: If a string in column A does not contain a specific string, put a string in column B next to corresponding cells

I would like to check a string in column 1 of an intelligent table that consits of 6 columns if it does not contain specific strings using INSTR. If so, then I would like to put a defined string (for example "MC") only in column 2 next to the corresponding cells from column 1.

With the following code it puts "MC" not only in the column next to column 1, but in 6 columns next to column 1. It seems that my function uses the amount of columns whithin the DataBodyRange (which is 6) and adds them behind the Offset to column1.

How can I put the string "MC" only in 1 of 6 column?

Sub MyFunction()

Dim rngConfigCodeTbl  As Range
Const MnS3 As String = "MnS1"
Const MnS3 As String = "MnS2"

Set rngConfigCodeTbl = xlsReferences.ListObjects("tblConfigCode").DataBodyRange

For Each rngConfigCodeTbl In rngConfigCodeTbl
    If InStr(rngConfigCodeTbl, MnS1) = 0 Or InStr(rngConfigCodeTbl, MnS2) = 0 Then rngConfigCodeTbl.Offset(0, 1) = "MC"
Next rngConfigCodeTbl

End Sub

>Solution :

The main point is your If-condition: You are checking if the cell value doesn’t contain MnS1 OR doesn’t contain MnS2. That means unless you have a cell that contain both strings, either the first or the second part of the condition is true.

You will simply need to change the Or to an And: Write something in the neighbor cell if the cell neither contains MnS1 nor MnS2.

There are 2 other things that should be fixed in your code: You declare (twice) the constant MnS3 (that gives, btw, a compile error), but you are accessing constants MnS1 and MnS2 in your code which are not declared.

And while For Each rngConfigCodeTbl In rngConfigCodeTbl surprisingly works, you should use a different run variable in the For-Loop:

Option Explicit

Sub MySub()
    Dim rngConfigCodeTbl  As Range, cell As Range
    Const MnS1 As String = "MnS1"
    Const MnS2 As String = "MnS2"
    
    Set rngConfigCodeTbl = xlsReferences.ListObjects("tblConfigCode").ListColumns(1).DataBodyRange
    
    For Each cell In rngConfigCodeTbl
        If InStr(cell, MnS1) = 0 And InStr(cell, MnS2) = 0 Then cell.Offset(0, 1) = "MC"
    Next cell 
End Sub

And you shouldn’t name a Subroutine MyFunction – it is not a function, it’s a subroutine.

Update: Ike (in the comments) is right, you probably want to check only one column. I updated the code.