Four examples to understand the function Instr() on VBA:
Example 1: Instr() return the position of a character in a word
Sub function_instr()
'Instr syntax: InStr( [start], string, substring, [compare] )
'VBA Excel search the position of a character in a word - integer
'ex 1: check at what position is the letter x contained in word "domyexcel.com"
result_ex1 = InStr("domyexcel.com", "x")
MsgBox ("character x is at the " & result_ex1 & "th position in the world domyexcel.com")
End sub
Example 2: Instr() presence of a character in a word (True or False)
Sub function_instr()
'VBA Excel check if a letter is included in a word - true or false
'ex 2: check if yes or no the letter x contained in word "domyexcel.com"
If InStr("domyexcel.com", "x") > 0 Then
result_ex2 = True
MsgBox ("letter x is included in domyexcel.com")
Else
result_ex2 = False
MsgBox ("letter x is not included in domyexcel.com")
End If
End Sub
Example 3: Instr() presence and location of a word in a database
'VBA Excel search if a world is contained in a database:
'ex3: tell at which line of the database the world "domyexcel" was found
For i = 1 To 10
If InStr(Cells(i, 1), "domyexcel.com") > 0 Then
result_ex3 = i
MsgBox ("The word domyexcel.com was found at row " & result_ex3)
End If
Next
End Sub
Example 4: Instr() Highlight the cell in a database that contain a specific character or word
Sub function_instr()
'VBA Excel highlight the cells in a database that contained a specific word a/o character
'ex4: highlight in blue the cells that contain the character x
For i = 1 To 10
If InStr(Cells(i, 4), "x") > 0 Then
Cells(i, 4).Select
Selection.Interior.Color = 16750899
End If
Next
End Sub
