Excel InStr VBA Function VBA
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.
The Function:
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:
Function ShowThemInStr()
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 & "!"
Exit For
End If
Next
End Function
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.
|
