'if range of cells contains string then goto

I know this is a topic that is talks about a lot, but I just cant find the answer.

I have a range. if in that range there is a word then I need it to go to the next code. if not, than skip the next code and move on.

I'm unable to define the range.

this is my code:

For a = 4 To 20
    If (Range("H" & a).Value = "*nice*") Then
        GoTo Clean 
    Else
        GoTo pre
    End If
Next

In the range I do have the word "nice" but it still skips clean and goes straight to pre.

whats wrong? how do i define my range and how can the code understand that in my range the word nice" IS IN there.

vba excel

Solution 1:^[1]

You can use the Like with the wildcard (*) before and after "nice", like :

If Range("H" & a).Value Like "*nice*" Then

Adding this line into your entire code:

Option Explicit

Sub TestLike()

Dim a As Long

For a = 4 To 20
    If Range("H" & a).Value Like "*nice*" Then
        GoTo Clean
    Else
        GoTo pre
    End If
Next

End Sub

Solution 2:^[2]

If (Range("H" & a).Value = "*nice*") Then

change to

If instr(Range("H" & a).Value,"nice")>0 Then

Solution 3:^[3]

Try something like this:

Option Explicit
Sub TestMe()
    Dim a As Long

    For a = 4 To 20
        If InStr(1, Range("H" & a).Value, "nice") Then
            GoTo Clean
        Else
            GoTo pre
        End If
    Next code here

End Sub

Just one big reminder - try to avoid goto statements, as far as they are considered really bad practise in VBA develelopment. What you can do is to create another sub routine and to call it from the code.

Solution 4:^[4]

At the moment you're looking for the string *nice*. The asterisks are not being treated as wildcards.

You can use the Like operator, this will then allow you to use asterisks as wildcards:

If (Range("H" & a).Value Like "*nice*") Then

You can then expand on this; if you only want the word 'nice' to match, and not other words that contain 'nice', you can use the below:

If " " & UCase((Range("H" & a).Value) & " " Like "[!A-Z]*NICE*[!A-Z]"

Whilst using Like is slower than using InStr, it allows more complex matching, due to it's similar syntax to RegEx.

I would also suggest, that instead of using the GoTo statement, you use a function or sub procedure for each piece of code that you wish to run.

Solution 5:^[5]

I ran into the same problem, and for me the best solution was the following.

If Not Range("H4:H20").Find(What:="nice", LookAt:=xlPart) Is Nothing Then

If an exact match is sought, the xlWhole keyword must be used.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution	Source
Solution 1	Shai Rado
Solution 2	Mak
Solution 3	Vityata
Solution 4	luke_t
Solution 5	user3715059

'if range of cells contains string then goto

Solution 1:[1]

Solution 2:[2]

Solution 3:[3]

Solution 4:[4]

Solution 5:[5]