How To Search A String With The VBA Instr Function

The VBA Instr Function is very useful for searching a string
for a particular piece of text.

If for example you want to loop a column or a field in
a table of multiple rows, and want to identify prize rows that are 81.51, you would write something
like this:

Sub VBAInstrFunction()
    Dim dblTemp As Double
    Dim rst As Object
    Dim strSQL As String
    
    Dim intThere As Integer
    
    strSQL = "SELECT p_PrizePrice FROM Prizes "
    
    Set rst = New ADODB.Recordset
    
    rst.Open strSQL, CurrentProject.Connection
    
    Do Until rst.EOF
        dblTemp = rst.Fields("p_PrizePrice")
        intThere = InStr(1, "$81.51", dblTemp)
        
        If intThere > 1 Then
            MsgBox "found it"
        End If
        
        rst.MoveNext
    Loop
    
    rst.Close
    Set rst = Nothing
    
End Sub

 

Here is the table I am using:

VBA_Instr_Function

As you can see the VBA Instr Function is very useful.

Let me know if you have any specific questions


Comments are closed.