How To Search A String With The VBA Instr Function
by admin on Sunday, October 25th, 2020 | No Comments
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:
As you can see the VBA Instr Function is very useful.
Let me know if you have any specific questions