CODEWITHSUNDEEP
vbaexcel
aj_bk
aj_bk

Reputation: 194

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.

Upvotes: 3

Views: 8448

Answers (5)

user3715059
user3715059

Reputation: 11

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.

Upvotes: 0

luke_t
luke_t

Reputation: 2985

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.

Upvotes: 1

Shai Rado
Shai Rado

Reputation: 33692

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

Upvotes: 2

Vityata
Vityata

Reputation: 43595

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.

Upvotes: 1

Mak
Mak

Reputation: 154

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

change to 

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

Upvotes: 1

Related Questions