Is there a limit to how many keywords you can put in a macro looking for the presence of multiple keywords?

John Carney
John Carney used Ask the Experts™
on
Here's how many I have in my current VBA code. Ideally I would like to include twice as many. Will that still work? This is what I have so far:

Sub z_ResetsExclude()
Application.Calculation = xlCalculationManual
For Each cel In Range([T11], [T1000000].End(xlUp))
'cel.Select
      If (InStr(UCase(cel), "RESET") > 0 Or InStr(UCase(cel), "REBOOT") > 0) _
      And (InStr(UCase(cel), "2 TIME") = 0 And InStr(UCase(cel), "TWICE") = 0 _
      And (InStr(UCase(cel), "NOT WORK") = 0 And InStr(UCase(cel), "THRICE") = 0 _
      And InStr(UCase(cel), "INNEFFECTIVE") = 0 And InStr(UCase(cel), "3 TIME") = 0 _
      And InStr(UCase(cel), "SEVERAL RESET") = 0 And InStr(UCase(cel), "UNABLE TO RE") = 0 _
      And InStr(UCase(cel), "FEW TIME") = 0 And InStr(UCase(cel), "MANY TIME") = 0 _
      And InStr(UCase(cel), "NO AVAIL") = 0 And InStr(UCase(cel), "FAULT REMAINS") = 0 _
      And InStr(UCase(cel), "STILL ") = 0 And InStr(UCase(cel), "COULD NOT") = 0 _
      And InStr(UCase(cel), "KEEPS ON RE") = 0 And InStr(UCase(cel), "3X") = 0 _
      And InStr(UCase(cel), "KEEP ON RE") = 0 And InStr(UCase(cel), "MANY RE") = 0 _
      And InStr(UCase(cel), "NOT BE RESET") = 0 And InStr(UCase(cel), "4X") = 0 _
      And InStr(UCase(cel), "FAIL") = 0 And InStr(UCase(cel), "NIL HELP") = 0 _
      And InStr(UCase(cel), "NO CHANGE") = 0 And InStr(UCase(cel), "CONSTANTLY") = 0 _
      And InStr(UCase(cel), "CAN'T BE RESET") = 0 And InStr(UCase(cel), "3 RESET") = 0 _
      And InStr(UCase(cel), "NON RESP") = 0 And InStr(UCase(cel), "NOT RESP") = 0 _
      And InStr(UCase(cel), "UNSUCCESSFUL") = 0 And InStr(UCase(cel), "NOT SUCCESS") = 0) _
      And InStr(UCase(cel), "NO HELP") = 0 And InStr(UCase(cel), "EVEN ") = 0) _
      Or InStr(UCase(cel), "WIFI") > 0 _
   Then
      cel = cel & "   ^^^ 0%"
      cel.Interior.Color = RGB(102, 255, 255)
   Else:
   End If
   
Next
End Sub

Open in new window




Thanks,
John
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Older than dirt
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
Yes, I believe it will.
John CarneyReliability Business Tools Analyst II

Author

Commented:
Martin, if you say so then in my book it must be true!

Thanks,
John
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Well, I obviously can't test it so...
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

NorieAnalyst Assistant
Commented:
John

There's no real limit but there's probably a better approach.

For example you could store all the keywords in a list on a worksheet, read that list in at the start of the code and then loop through it.

By the way, as far as I can see the posted code is only actually checking for two terms, 'RESET' and 'REBOOT' being in the cell, for all the other terms you appear to be checking that they are not in the cell
John CarneyReliability Business Tools Analyst II

Author

Commented:
Hi Norie, yes I need to identify the cells with those to keywords and add a string at the end unless it contains any of the other keywords. I'll see if I can figure out the exact syntax of the list reading method.

Martin and Norie, what I discovered is that there is a limit to the number of line skips you can write. So I'm doubling it up to 4 per line and we'll see how that works.

Thanks,
John
John CarneyReliability Business Tools Analyst II

Author

Commented:
Thank you both.

John
Martin LissOlder than dirt
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you including a new one concerning your Experts Exchange rank.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2018
              Experts Exchange Top Expert VBA 2018
              Experts Exchange Distinguished Expert in Excel 2018

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial