Check if a cell contains specific text

Question

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.

Warning: extensive use of relative addressing in macros is pretty dangerous. if you don't open your workbooks in the right order or select the right cells, you are doomed. Try to avoid all .select and all .actvate if you can! — nutsch, May 22 '15 at 17:15
Check out Jon Peltier's blog for some macro cleanup tips: http://peltiertech.com/how-to-fix-a-recorded-macro/ — nutsch, May 22 '15 at 17:17
Also, [How to avoid using Select in Excel VBA macros](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) is a great resource for eliminating `Active`, `Select`, etc... — FreeMan, May 22 '15 at 17:32
Edit your question to include the names of the worksheets and the particular columns that you are looking through. There is a good chance someone will take the time to kickstart your code into removing the reliance on ActiveWindow and ActiveCell/Select. — , May 22 '15 at 18:47
I don't mind that reliance, it's not fixing the problem with my code, so get over this. — GoombaJoe, May 26 '15 at 11:12

FreeMan · Accepted Answer · 2015-05-22T17:40:31.180

2

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

edited May 22 '15 at 17:40

answered May 22 '15 at 17:09

FreeMan

5,660
1
27
53

In what way did it "not work"? Maybe post a screen shot of your data (scrubbed for anything personal). – FreeMan May 22 '15 at 17:32
It's returning a job listed as completed. Column 6 contains "On Hold", "Ongoing" or "Completed"...as stated before, sometimes there's a date in the cell with "Completed". – GoombaJoe May 22 '15 at 17:35
1

So, it's processing a cell that contains "Completed", but when the `If` statements I provided execute, it drops to the `Else` and executes `ActiveCell.Offset(0, -1).Select`, when you're expecting it to execute `ActiveCell.Offset(1, -6).Select`? Maybe try the updates in my answer - don't think those will impact it, though. – FreeMan May 22 '15 at 17:39
If UCase(ActiveCell.Value) like "*COMPLETED*" Then ---- This worked perfectly, thank you! – GoombaJoe May 26 '15 at 11:30

Check if a cell contains specific text

1 Answers1