asked on

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

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

ASKER CERTIFIED SOLUTION

Martin Liss

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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

Thanks,
John

Martin Liss

Well, I obviously can't test it so...

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

John Carney

ASKER

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 Carney

ASKER

Thank you both.

John

Martin Liss

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