Excel InStr VBA Function: What It Is and How to Use It?
The Excel InStr function is a great function to know in Excel VBA. This function provides a way of querying a string to find the first occurence of one string within another. You can set a static variable and compare it to a dynamic variable (in a loop or such) that when found, can perform some sort of logic the user specifies.
InStr([ Start], FirstString, SecondString[, Compare])
Start: Integer. This is optional, you can choose to start looking at a string a certain character in the string (i.e. 3 would start 3 charcters in)
FirstString:1st string you are comparing
SecondString:String you are comparing the first string to
Compare:This is optional. Below are the listed options for this value:
vbUseCompareOption: – 1 … A comparison using the setting of the Option Compare statement
vbBinaryCompare: 0 … This a binary comparison
vbTextCompare: 1 This will compare textually
Example of InStr function:
Dim getstring1 As String
Dim getstring2 As String
Dim CompareTheTwo As Boolean
Dim currentrow As Integer
CompareTheTwo = False
For currentrow = 1 To Sheets("Sheet1").UsedRange.Rows.Count
ChangingText = Sheets("Sheet1").Cells(currentrow, 1).Value
CompareTheTwo = InStr(ChangingText, "Goose")
If CompareTheTwo = True Then
MsgBox "You Found the word Goose in cell " & currentrow & "!"
What this function does is cycle through all of the cells in Column A (1) until it finds the word “Goose”. If “Goose” is found, which would mean the variable was true since we dimmed it as a Boolean operator, the IF statement would take over and the logic would be performed. In this case, a message box would pop up and tell you what row the word “Goose” was found in.