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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

salim hasan

Board Regular
Joined
Dec 25, 2013
Messages
105
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:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
If you want to check the entire value just use
Code:
a(i, j) = "err"
 

DataBlake

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
56,526
Office Version
  1. 365
Platform
  1. Windows
Glad it's sorted & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,129,479
Messages
5,636,574
Members
416,925
Latest member
malamutus

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
Top