Make InStr with entire cell qualifier

DataBlake

Well-known Member
Joined
Jan 26, 2015
Messages
781
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Try this macro
Code:
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
ABCDEFG
1Rasmk21Asm45Asmword to find
231A31Asm
31943AM$A$1
4Asm21A17$C$1
5397A25$D$2
63249Asm$A$4
7$C$6

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
 
Last edited:
Upvote 0
If you want to check the entire value just use
Code:
a(i, j) = "err"
 
Upvote 0
Code:
a(i, j) = "err"

Code:
    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?
 
Upvote 0
Best way to find out if it works is to try it. ;)
 
Upvote 0
Glad it's sorted & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,511
Messages
6,114,054
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top