CODEWITHSUNDEEP
vbaexcel
GoombaJoe
GoombaJoe

Reputation: 45

Check if a cell contains specific text

I have to put jobs from one spreadsheet onto another in their priority order. If a job is listed as completed, then I do not transfer that job over. Below is my code for the top priority, "priority 1". The cell that states it's completion status sometimes has a date before or after it, which is why I put the "*" character.

Do Until IsEmpty(ActiveCell) Or count > 14
                If ActiveCell.Value = "Priority I" Then
                    ActiveCell.Offset(0, 6).Select
                    If ActiveCell.value = "completed" like "*completed*" Then
                    ActiveCell.Offset(1, -6).Select
                    Else
                    ActiveCell.Offset(0, -1).Select
                    word0 = ActiveCell.Value
                    ActiveWindow.ActivateNext
                    ActiveCell = word0
                    ActiveWindow.ActivateNext
                    ActiveCell.Offset(0, -9).Select
                    word = Left(ActiveCell.Value, 6)
                    ThisWorkbook.Activate
                    ActiveCell.Offset(0, 1).Select
                    ActiveCell = word
                    ActiveWindow.ActivateNext
                    ActiveCell.Offset(0, 1).Select
                    word1 = ActiveCell.Value
                    ThisWorkbook.Activate
                    ActiveCell.Offset(0, 1).Select
                    ActiveCell.Value = word1
                    ActiveWindow.ActivateNext
                    ActiveCell.Offset(0, 1).Select
                    word2 = ActiveCell.Value
                    ThisWorkbook.Activate
                    ActiveCell.Offset(0, 1).Select
                    ActiveCell.Value = word2
                    ActiveWindow.ActivateNext
                    ActiveCell.Offset(0, 1).Select
                    word3 = ActiveCell.Value
                    ThisWorkbook.Activate
                    ActiveCell.Offset(0, 1).Select
                    ActiveCell.Value = word3
                    ActiveCell.Offset(1, -4).Select
                    ActiveWindow.ActivateNext
                    ActiveCell.Offset(1, 1).Select
                    count = count + 1
                    End If
                    Else
                    ActiveCell.Offset(1, 0).Select
               End If
            Loop

I have confirmed that it is checking the correct column, it just doesn't catch the word completed. So the problem resides within that line, line 4.

Upvotes: 1

Views: 4111

Answers (1)

FreeMan
FreeMan

Reputation: 5687

Change 

 If ActiveCell.value = "completed" like "*completed*" Then

to

If Instr(1, UCase(ActiveCell.Value), "COMPLETED") > 0 Then

or

If UCase(ActiveCell.Value) like "*COMPLETED*" Then

Upvotes: 2

Related Questions