What is InStr in VBA

 

🔍 InStr – What It Does

The InStr function returns the position of the first occurrence of a substring within another string.
If the substring is not found, it returns 0.

---

✅ Syntax

InStr([start], string1, string2, [compare])

🔧 Parameters

Parameter	Required?	Description
start	Optional	Position to start the search (default is 1).
string1	Required	The main string to search in.
string2	Required	The substring to search for.
compare	Optional	Comparison type: vbBinaryCompare (case-sensitive) or vbTextCompare (case-insensitive).

---

🧠 Returns

• A number indicating the position (1-based index) of the first match.

• 0 if string2 is not found in string1.

---

📌 Example 1: Simple Match

Dim pos As Integer
pos = InStr("Welcome to VBA", "VBA")
' pos = 12

Explanation: "VBA" starts at the 12th character in "Welcome to VBA".

---

📌 Example 2: Case-Insensitive Match

Dim pos As Integer
pos = InStr(1, "Hello World", "world", vbTextCompare)
' pos = 7

Explanation: The vbTextCompare makes it case-insensitive, so "world" is found at position 7.

---

📌 Example 3: Case-Sensitive Match Fails

Dim pos As Integer
pos = InStr(1, "Hello World", "world", vbBinaryCompare)
' pos = 0

Explanation: This is case-sensitive, and "world" ≠ "World", so no match is found.

---

🔁 In Your Code Example

From your earlier code:

If InStr(1, LCase(ws.Cells(i, 1).Value), LCase(SearchTerm)) > 0 Then
    matchFound = True
End If

What’s happening here?

• LCase(...): converts both the cell value and search term to lowercase.

• InStr(...) > 0: checks if the search term exists in the cell value.

✅ This allows case-insensitive search of customer names in column A.