VBA Instr Function |

VBA Instr Function

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

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 […]

VBA Do Loop

Looping structures like the VBA Do Loop allow you to specify the conditions under which you would like your program to run a set of code statements repeatedly. There are 4 different variations of the VBA Do Loop we are going to look at: ‘Do While…Loop structures check the condition before running the code. ‘The […]