CODEWITHSUNDEEP
vbaexcel
lisa_rao007
lisa_rao007

Reputation: 357

How can I find exact Instr value

I am trying find exact numbers instead of finding whole number. Something like this 456 or 678. Every time when I ran my code, I end up finding the whole number(45677777) instead of just 456. Just to make it clear, I have few numbers in the same rows something like ( 456, 456777777, 677, 456, 456666666 so on). I am trying to make my InStr to avoid the numbers with 4567777.

enter image description here

Code:

 dim b as string 
   b = "-456-"
    for i = 1 to lastrow
     if instr( 1, worksheets("sheet1").range("A" & i), b > 0 then
      msgbox yes
     end if 
  next i

Upvotes: 0

Views: 1610

Answers (4)

Richard Huynh
Richard Huynh

Reputation: 76

You should compare by using the = operator if you want exact matches instead of InStr.

  b = "456"
    For i = 1 To 7
     If (b = Cells(1, i).Value) Then
      Debug.Print ("Yes")
     Else
      Debug.Print ("No")
     End If
  Next i

If the values that you were trying to test had those hyphen characters, then you would have to parse them out before doing any comparisons.

Also if you're working with ranges, I found that importing ranges into arrays and working with the arrays instead of the actual cells and their values is easier and also faster. EDIT: To make it more dynamic so you don't have to hardcode what the range you're looking for is and you want the sub to do this until there isn't a value to look at in the column anymore, you can use the Find method.

This would work for your example as so:

Dim arrVals()
lastrow = Cells.Find("*", , xlValues, , xlByRows, xlPrevious).Row
strRangeToCapture = "A1:A" & CStr(lastrow)
arrVals = Range(strRangeToCapture)

Dim b As String
b = "456"

For i = 1 To lastrow
    If (b = arrVals(i, 1)) Then
        Debug.Print ("Yes")
    Else
        Debug.Print ("No")
    End If
Next

Hope this helps! :)

Upvotes: 4

Vasily
Vasily

Reputation: 5782

Try this approach:

data sample:

enter image description here

the code:

Sub test()
Dim cl As Range, key As Variant, Data As Range, criteria$
criteria = "456"
Set Data = Range([A1], Cells(Cells(Rows.Count, "A").End(xlUp).Row, "A"))
For Each cl In Data
    For Each key In Split(cl.Value, ",")
        If Trim(key) = criteria Then
            MsgBox "Row: " & cl.Row & " has a value: " & cl.Value & " matched with search string"
        End If
    Next key
Next cl
End Sub

Upvotes: 1

L42
L42

Reputation: 19727

From MSDN, InStr

Returns an integer specifying the start position of the first occurrence of one string within another

So what you observe is the correct behavior of the said function.
To get what you want, you can go with what Richard posted or since you are using Excel, you can try using Find method of the range object which have some benefits in terms of speed for large data sets.

Something like:

Dim r As Range, found As Range
Dim b As String, FAdd As String
Dim lastrow As String

With Worksheets("Sheet1")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
    Set r = .Range("A1:A" & lastrow): b = "456"
    Set found = r.Find(b, .Range("A" & lastrow) , , xlWhole)
End With

If Not found Is Nothing Then FAdd = found.Address Else MsgBox "No match found.": Exit Sub

Do
    Debug.Print found.Address
    Set found = r.FindNext(After:=found)
Loop While FAdd <> found.Address

Upvotes: 0

MatthewD
MatthewD

Reputation: 6761

You example data doesn't show it but from your description it sounds like you have cells that have comma delimited values in them.

Split the values from the cells and then look at each value for an exact match.

ColumnA
---------------------
444, 456777, 456, 888
777
444, 456777, 444, 888
456

This will find Rows 1 and 4 only.

Private Sub CommandButton5_Click()
    Dim lRow As Long
    Dim ws As Excel.Worksheet
    Dim strValues() As String
    Dim i As Integer
    Dim b As String

    b = "456"

    Set ws = Application.ActiveSheet

    'Loop through all the rows.
    lRow = 1
    Do While lRow <= ws.UsedRange.Rows.Count

        'Get the values
        strValues = Split(ws.Range("A" & lRow).Value, ",")

        For i = 0 To UBound(strValues)
            If strValues(i) = b Then
                MsgBox ("Row " & Str(lRow) & " has a match.")
            End If
        Next i

        lRow = lRow + 1
    Loop
End Sub

Upvotes: 1

Related Questions