CODEWITHSUNDEEP
vbaexcel
synthaxe
synthaxe

Reputation: 95

Syntax: using IF statement in excel-vba

I want to write an If statement that will satisfy the condition with just using one word. For example:

if sheet1.cells(1,1)="a quick brown fox" then
end if

What I want to happen is even if there's only the word "quick", the condition would be satisfied.

Upvotes: 0

Views: 81

Answers (1)

Bond
Bond

Reputation: 16311

You can use the InStr() function to test a string for a substring:

If InStr(Sheet1.Cells(1, 1), "quick") > 0 Then
    ' Cell contains the string "quick"
End If

For a case-insensitive comparison, you must supply all four possible arguments to the function:

If InStr(1, Sheet1.Cells(1, 1), "quick", vbTextCompare) > 0 Then
    ' Cell contains the string "quick" or "QUICK" or any combination of upper/lowercase
End If

As @AndyG mentions in the comments below, you can also use the Like operator with wildcards to test if a string contains a substring:

If Sheet1.Cells(1, 1) Like "*quick*" Then          ' Case-sensitive
-or-
If LCase$(Sheet1.Cells(1, 1)) Like "*quick*" Then  ' Case-insensitive

Note that these methods would also match "quickly" and other words containing the string "quick". If you want to get more specific, a regex may work better. Add a reference to the Microsoft VBScript Regular Expressions 5.5 library and you can use the following:

Dim re As New RegExp
re.IgnoreCase = False       ' Up to you
re.Pattern = "\bquick\b"    ' Match the word "quick"

If re.Test(Sheet1.Cells(1, 1)) Then
    ' Cell contains the word "quick"
End If

Upvotes: 3

Related Questions