If Is Equal To

[tomgrandy]

Currently I have a string as:

If InStr(cell.Value, "DRILL") <> 0 Then
cell.Offset(0, 7).Value = "POINT"
End If

It needs to read that if that cell value equals DRILL then write the value of POINT

Problem that I am encountering is that where the descriptions it is reading for values from has the word "Drilled" as in a tooth that was drilled (not a Point) that is shows another value.

So, how do I rewrite the above to ONLY pick up Drill and not Drilled?

Thanks in advance,

Tom

 

[tomgrandy]

I'm thinking this should do it:

If InStr(cell.Value, "DRILL") Then
cell.Offset(0, 7).Value = "POINT"
End If

 

[tomgrandy]

That didn't work. Need it to be exactly equal to DRILL so as not to grab the value of DRILLED

 

[MARK858]

Maybe

If InStr(cell.Value, " DRILL ") Then
cell.Offset(0, 7).Value = "POINT"
End If

 

[tomgrandy]

Maybe

If InStr(cell.Value, " DRILL ") Then
cell.Offset(0, 7).Value = "POINT"
End If

Is it the spaces before and after DRILL that make it equal to?

 

[MARK858]

It is for the example you have given

 

[tomgrandy]

It is for the example you have given

Most excellent - thank you very much!!

 

[MARK858]

You're welcome

Btw you don't need the End If when doing a single test, you can just write it as a single line

If InStr(cell.Value, " DRILL ") Then cell.Offset(0, 7).Value = "POINT"

 

[tomgrandy]

You're welcome

Btw you don't need the End If when doing a single test, you can just write it as a single line

If InStr(cell.Value, " DRILL ") Then cell.Offset(0, 7).Value = "POINT"

What if I am writing line after line like these (literally hundreds). Is there a more efficient way for the macro to run?

        If InStr(cell.Value, "PENDANT") > 0 Then
            cell.Offset(0, 8).Value = "PENDANT AND GORGETS"
        End If
        
        If InStr(cell.Value, "GORGET") > 0 Then
            cell.Offset(0, 8).Value = "PENDANT AND GORGETS"
        End If
        
        If InStr(cell.Value, "BANNERSTONE") > 0 Then
            cell.Offset(0, 8).Value = "BANNERSTONES & ATL-ATL WEIGHTS"
        End If
        
       If InStr(cell.Value, "BANNERS") > 0 Then
            cell.Offset(0, 8).Value = "BANNERSTONES & ATL-ATL WEIGHTS"
        End If
        
        If InStr(cell.Value, "LOAFSTONE") > 0 Then
            cell.Offset(0, 8).Value = "BANNERSTONES & ATL-ATL WEIGHTS"
        End If
        
        If InStr(cell.Value, "BAR AMULET") > 0 Then
            cell.Offset(0, 8).Value = "BANNERSTONES & ATL-ATL WEIGHTS"
        End If

 

[MARK858]

Don't know about more efficient but maybe easier to write (Change myCell to suit)

    Dim MyArr, MyArr2, myCell As Range
    Dim MyFind As Integer, MyFind2 As Integer

    Set myCell = Range("A4")

    MyArr = Array("PENDANT", "GORGET")
    MyArr2 = Array("BANNERSTONE", "BANNERS", "LOAFSTONE", "BAR AMULET")

    For MyFind = LBound(MyArr) To UBound(MyArr)
        If InStr(myCell.Value, " " & MyArr(MyFind) & " ") > 0 Then myCell.Offset(0, 8).Value = "PENDANT AND GORGETS"
    Next
        
    For MyFind2 = LBound(MyArr2) To UBound(MyArr2)
        If InStr(myCell.Value, " " & MyArr2(MyFind2) & " ") > 0 Then myCell.Offset(0, 8).Value = "BANNERSTONES & ATL-ATL WEIGHTS"
    Next