Make InStr with entire cell qualifier

[DataBlake]

Is it possible to use InStr to where it will only return the position of a string if the phrase you are looking for is the entire cell.
So say for example

InStr(1, a(i, j), "err", 1)

i want to find "err" in an array and not have it return "Sierra"
only if the entire cell is "err" do i want it to return an integer
any known work arounds for this?

 

[salim hasan]

Try this macro

Option Explicit

Sub find_for_me()
Dim my_rg As Range, My_Cel As Range
Dim Adres(), k%: k = 1
Dim txt: txt = UCase(Range("F1"))
Range("F3").CurrentRegion.ClearContents
Set my_rg = Range("A1:D6")
For Each My_Cel In my_rg
      If InStr(UCase(My_Cel), txt) Then
        ReDim Preserve Adres(1 To k)
        Adres(k) = My_Cel.Address
        k = k + 1
      End If
 Next
If k > 1 Then _
 Range("F3").Resize(UBound(Adres)) = _
 Application.Transpose(Adres)
End Sub

	A	B	C	D	E	F	G
word to find

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]Rasmk[/TD]
[TD="align: center"]21[/TD]
[TD="bgcolor: #FFFF00, align: center"]Asm[/TD]
[TD="align: center"]45[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #92D050, align: center"]Asm[/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]31[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]31[/TD]
[TD="bgcolor: #FFFF00, align: center"]Asm[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]43[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF00, align: center"]$A$1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFFF00, align: center"]Asm[/TD]
[TD="align: center"]21[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]17[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF00, align: center"]$C$1[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]39[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]25[/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF00, align: center"]$D$2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]32[/TD]
[TD="align: center"]49[/TD]
[TD="bgcolor: #FFFF00, align: center"]Asm[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF00, align: center"]$A$4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="bgcolor: #FFFF00, align: center"]$C$6[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

 

[Fluff]

If you want to check the entire value just use

a(i, j) = "err"

 

[DataBlake]

a(i, j) = "err"

    If Not IsError(a(i, j)) Then
      Select Case True
        Case InStr(1, a(i, j), "trailer", 1) > 0, _
              InStr(1, a(i, j), "dually", 1) > 0, _
              InStr(1, a(i, j), "duallie", 1) > 0, _
              InStr(1, a(i, j), "dualie", 1) > 0, _
              InStr(1, a(i, j), "atv", 1) > 0, _
              InStr(1, a(i, j), "utv", 1) > 0, _
              InStr(1, a(i, j), "blank", 1) > 0, _
              a(i, j) = "err"

this works then?
i haven't tested it, but essentially if any cell in my worksheet contains words listed or if the cell is entirely just "err" then the case is selected right?

 

[Fluff]

Best way to find out if it works is to try it.

 

[DataBlake]

Best way to find out if it works is to try it.

it SEEMS to have worked
no lone "err" s but i still have Sierras

 

[Fluff]

Glad it's sorted & thanks for the feedback