Help with If VBA Statement [Solved/Closed]

Report
-
 Boysenb -
Hello,

I have this code that i can not get to work all the way i get the error of "Too many local, nonstatic variables" i am new to this and all it does is check our survey codes and tell me what ones are bad.

Is there an easy way of fixing this?

Thanks for your help

finalrow = ActiveSheet.UsedRange.Rows.Count
Count = 1

For D = 2 To finalrow

If Cells(D, 5) = "AB" Or Cells(D, 5) = "AP" Or Cells(D, 5) = "APAN" Or Cells(D, 5) = "AT" Or Cells(D, 5) = "BARN" Or Cells(D, 5) = "BD" Or Cells(D, 5) = "BERM" Or Cells(D, 5) = "BK" Or Cells(D, 5) = "BLO" Or Cells(D, 5) = "BM" Or Cells(D, 5) = "BOC" Or Cells(D, 5) = "BOLL" Or Cells(D, 5) = "BRE" Or Cells(D, 5) = "BRK" Or Cells(D, 5) = "CAN" Or Cells(D, 5) = "CCR" Or Cells(D, 5) = "CFP" Or Cells(D, 5) = "CFR" Or Cells(D, 5) = "CG" Or Cells(D, 5) = "CI" Or Cells(D, 5) = "CK" _
Or Cells(D, 5) = "CLR" Or Cells(D, 5) = "COMP" Or Cells(D, 5) = "COOL" Or Cells(D, 5) = "CP" Or Cells(D, 5) = "CPI" Or Cells(D, 5) = "CPR" Or Cells(D, 5) = "CRK" Or Cells(D, 5) = "CROPC" Or Cells(D, 5) = "CSH" Or Cells(D, 5) = "CTR" Or Cells(D, 5) = "CUL" Or Cells(D, 5) = "DEHY" Or Cells(D, 5) = "DRN" Or Cells(D, 5) = "DT" Or Cells(D, 5) = "EFD" Or Cells(D, 5) = "ELA" Or Cells(D, 5) = "ELO" Or Cells(D, 5) = "ENTIM" Or Cells(D, 5) = "EOL" Or Cells(D, 5) = "EP" Or Cells(D, 5) = "ER" _
Or Cells(D, 5) = "ERK" Or Cells(D, 5) = "EROW" Or Cells(D, 5) = "ES" Or Cells(D, 5) = "ESD" Or Cells(D, 5) = "EWASH" Or Cells(D, 5) = "EWP" Or Cells(D, 5) = "EXV" Or Cells(D, 5) = "EOC" Or Cells(D, 5) = "EXVB" Or Cells(D, 5) = "FL" Or Cells(D, 5) = "FLANGE" Or Cells(D, 5) = "FO" Or Cells(D, 5) = "FOPED" Or Cells(D, 5) = "FW" Or Cells(D, 5) = "FWXR" Or Cells(D, 5) = "GA" Or Cells(D, 5) = "GR" Or Cells(D, 5) = "GRD" Or Cells(D, 5) = "GUYP" Or Cells(D, 5) = "HSE" Or Cells(D, 5) = "HYD" Or Cells(D, 5) = "IRR" _
Or Cells(D, 5) = "JB" Or Cells(D, 5) = "LP" Or Cells(D, 5) = "METERP" Or Cells(D, 5) = "MH" Or Cells(D, 5) = "MISC" Or Cells(D, 5) = "MM" Or Cells(D, 5) = "MR" Or Cells(D, 5) = "MRKR" Or Cells(D, 5) = "MSH" Or Cells(D, 5) = "MTR" Or Cells(D, 5) = "NG" Or Cells(D, 5) = "OB" Or Cells(D, 5) = "OHELC" Or Cells(D, 5) = "OHTLN" Or Cells(D, 5) = "PI" Or Cells(D, 5) = "PJ" Or Cells(D, 5) = "PL" Or Cells(D, 5) = "PLA" Or Cells(D, 5) = "PLB" Or Cells(D, 5) = "PLC" Or Cells(D, 5) = "PLD" _
Or Cells(D, 5) = "PLE" Or Cells(D, 5) = "PLF" Or Cells(D, 5) = "PLG" Or Cells(D, 5) = "PLH" Or Cells(D, 5) = "PLI" Or Cells(D, 5) = "PLJ" Or Cells(D, 5) = "PLK" Or Cells(D, 5) = "POT" Or Cells(D, 5) = "PP" Or Cells(D, 5) = "PPL" Or Cells(D, 5) = "PRC" Or Cells(D, 5) = "QUSCOR" Or Cells(D, 5) = "RAIL" Or Cells(D, 5) = "RD" Or Cells(D, 5) = "RDI" Or Cells(D, 5) = "RED" Or Cells(D, 5) = "RISE" Or Cells(D, 5) = "RIV" Or Cells(D, 5) = "ROW" Or Cells(D, 5) = "ROWF" Or Cells(D, 5) = "RP" _
Or Cells(D, 5) = "RR" Or Cells(D, 5) = "RRRW" Or Cells(D, 5) = "SAG" Or Cells(D, 5) = "SECCOR" Or Cells(D, 5) = "SEP" Or Cells(D, 5) = "SHD" Or Cells(D, 5) = "SIGN" Or Cells(D, 5) = "SL" Or Cells(D, 5) = "SS" Or Cells(D, 5) = "STAIR" Or Cells(D, 5) = "SXSCOR" Or Cells(D, 5) = "TANK" Or Cells(D, 5) = "TEE" Or Cells(D, 5) = "TEG" Or Cells(D, 5) = "TELEPED" Or Cells(D, 5) = "TGL" Or Cells(D, 5) = "TLC" Or Cells(D, 5) = "TLN" Or Cells(D, 5) = "TLOB" Or Cells(D, 5) = "TLT" Or Cells(D, 5) = "TOBAL" _
Or Cells(D, 5) = "TOEB" Or Cells(D, 5) = "TOELB" Or Cells(D, 5) = "TOES" Or Cells(D, 5) = "TOPB" Or Cells(D, 5) = "TOPHB" Or Cells(D, 5) = "TOPS" Or Cells(D, 5) = "TP" Or Cells(D, 5) = "TPBAL" Or Cells(D, 5) = "TPRAIL" Or Cells(D, 5) = "TREE" Or Cells(D, 5) = "TS" Or Cells(D, 5) = "TUA" Or Cells(D, 5) = "TVP" Or Cells(D, 5) = "UE" Or Cells(D, 5) = "UT" Or Cells(D, 5) = "VALVE" Or Cells(D, 5) = "VB" Or Cells(D, 5) = "WALL" Or Cells(D, 5) = "WASH" Or Cells(D, 5) = "WB" Or Cells(D, 5) = "WEG" _
Or Cells(D, 5) = "WF" Or Cells(D, 5) = "WH" Or Cells(D, 5) = "WINDMILL" Or Cells(D, 5) = "WL" Or Cells(D, 5) = "XANO" Or Cells(D, 5) = "XAP" Or Cells(D, 5) = "XAT" Or Cells(D, 5) = "XBB" Or Cells(D, 5) = "XBBP" Or Cells(D, 5) = "XBC" Or Cells(D, 5) = "XBCC" Or Cells(D, 5) = "XBCP" Or Cells(D, 5) = "XBD" Or Cells(D, 5) = "XBFD" Or Cells(D, 5) = "XBK" Or Cells(D, 5) = "XBLO" Or Cells(D, 5) = "XBPD" Or Cells(D, 5) = "XBRD" Or Cells(D, 5) = "XBRK" Or Cells(D, 5) = "XBRS" Or Cells(D, 5) = "XBS" _
Or Cells(D, 5) = "XCAN" Or Cells(D, 5) = "XCCR" Or Cells(D, 5) = "XCDW" Or Cells(D, 5) = "XCFR" Or Cells(D, 5) = "XCLR" Or Cells(D, 5) = "XCPR" Or Cells(D, 5) = "XCRK" Or Cells(D, 5) = "XCROPC" Or Cells(D, 5) = "XCSH" Or Cells(D, 5) = "XCTR" Or Cells(D, 5) = "XCUL" Or Cells(D, 5) = "XCW" Or Cells(D, 5) = "XDJ" Or Cells(D, 5) = "XDRN" Or Cells(D, 5) = "XDT" Or Cells(D, 5) = "XEB" Or Cells(D, 5) = "XEBP" Or Cells(D, 5) = "XEC" Or Cells(D, 5) = "XECC" Or Cells(D, 5) = "XECP" Or Cells(D, 5) = "XEFD" _
Or Cells(D, 5) = "XELA" Or Cells(D, 5) = "XELO" Or Cells(D, 5) = "XEOL" Or Cells(D, 5) = "XEP" Or Cells(D, 5) = "XEPD" Or Cells(D, 5) = "XER" Or Cells(D, 5) = "XERD" Or Cells(D, 5) = "XERK" Or Cells(D, 5) = "XEROW" Or Cells(D, 5) = "XERS" Or Cells(D, 5) = "XES" Or Cells(D, 5) = "XETIM" Or Cells(D, 5) = "XEWASH" Or Cells(D, 5) = "XEWP" Or Cells(D, 5) = "XFL" Or Cells(D, 5) = "XFLANGE" Or Cells(D, 5) = "XFO" Or Cells(D, 5) = "XFW" Or Cells(D, 5) = "XFWXR" Or Cells(D, 5) = "XIRR" Or Cells(D, 5) = "XLE" _
Or Cells(D, 5) = "XMISC" Or Cells(D, 5) = "XMSH" Or Cells(D, 5) = "XNG" Or Cells(D, 5) = "XOB" Or Cells(D, 5) = "XOHELC" Or Cells(D, 5) = "XOHTLN" Or Cells(D, 5) = "XPI" Or Cells(D, 5) = "XPL" Or Cells(D, 5) = "XPPL" Or Cells(D, 5) = "XR" Or Cells(D, 5) = "XRD" Or Cells(D, 5) = "XRDI" Or Cells(D, 5) = "XRED" Or Cells(D, 5) = "XRIV" Or Cells(D, 5) = "XROW" Or Cells(D, 5) = "XROWF" Or Cells(D, 5) = "XROWWC" Or Cells(D, 5) = "XRR" Or Cells(D, 5) = "XRRRW" Or Cells(D, 5) = "XSAG" Or Cells(D, 5) = "XSBB" _
Or Cells(D, 5) = "XSL" Or Cells(D, 5) = "XSW" Or Cells(D, 5) = "XTB" Or Cells(D, 5) = "XTEE" Or Cells(D, 5) = "XTEEL" Or Cells(D, 5) = "XTEER" Or Cells(D, 5) = "XTGL" Or Cells(D, 5) = "XTLC" Or Cells(D, 5) = "XTLN" Or Cells(D, 5) = "XTOBAL" Or Cells(D, 5) = "XTOEB" Or Cells(D, 5) = "XTOELB" Or Cells(D, 5) = "XTOES" Or Cells(D, 5) = "XTOPB" Or Cells(D, 5) = "XTOPHB" Or Cells(D, 5) = "XTOPLB" Or Cells(D, 5) = "XTOPS" Or Cells(D, 5) = "XTP" Or Cells(D, 5) = "XTPBAL" Or Cells(D, 5) = "XTPRAIL" Or Cells(D, 5) = "XTS" _
Or Cells(D, 5) = "XTTH" Or Cells(D, 5) = "XUE" Or Cells(D, 5) = "XUST" Or Cells(D, 5) = "XUT" Or Cells(D, 5) = "XVALVE" Or Cells(D, 5) = "XW" Or Cells(D, 5) = "XWASH" Or Cells(D, 5) = "XWB" Or Cells(D, 5) = "XWEG" Or Cells(D, 5) = "XWL" Or Cells(D, 5) = "XWTC" _
Or Cells(D, 5) = "ANO" Or Cells(D, 5) = "ARCHB" Or Cells(D, 5) = "ARCHE" Or Cells(D, 5) = "BB" Or Cells(D, 5) = "BBP" Or Cells(D, 5) = "BC" Or Cells(D, 5) = "BCC" Or Cells(D, 5) = "BCP" Or Cells(D, 5) = "BDD" Or Cells(D, 5) = "BPD" Or Cells(D, 5) = "BRD" Or Cells(D, 5) = "BRS" Or Cells(D, 5) = "BWL" Or Cells(D, 5) = "CDW" Or Cells(D, 5) = "CW" Or Cells(D, 5) = "DI" Or Cells(D, 5) = "DRP" Or Cells(D, 5) = "EB" Or Cells(D, 5) = "EBP" Or Cells(D, 5) = "EC" Or Cells(D, 5) = "ECC" Or Cells(D, 5) = "ECP" Or Cells(D, 5) = "EDD" Or Cells(D, 5) = "EPD" Or Cells(D, 5) = "ERD" Or Cells(D, 5) = "ERS" Or Cells(D, 5) = "ETIM" Or Cells(D, 5) = "ETIM" Or Cells(D, 5) = "EWL" Or Cells(D, 5) = "EXTIM" Or Cells(D, 5) = "GRADE" Or Cells(D, 5) = "LAUNCHER" Or Cells(D, 5) = "LE" Or Cells(D, 5) = "MHSE" Or Cells(D, 5) = "NF" Or Cells(D, 5) = "PAD" Or Cells(D, 5) = "PGC" Or Cells(D, 5) = "SBB" _
Or Cells(D, 5) = "SOLAR" Or Cells(D, 5) = "SW" Or Cells(D, 5) = "VENT" Or Cells(D, 5) = "W" Or Cells(D, 5) = "WC" Or Cells(D, 5) = "WTC" _
Or Cells(D, 5) = "XAB" Or Cells(D, 5) = "XAN" Or Cells(D, 5) = "XAPAN" Or Cells(D, 5) = "XBARN" Or Cells(D, 5) = "XBDD" Or Cells(D, 5) = "XBOC" Or Cells(D, 5) = "XBOLL" Or Cells(D, 5) = "XBRE" Or Cells(D, 5) = "XBWL" Or Cells(D, 5) = "XCFP" Or Cells(D, 5) = "XCOMP" Or Cells(D, 5) = "XDEHY" Or Cells(D, 5) = "XDI" Or Cells(D, 5) = "XDRP" Or Cells(D, 5) = "XEDD" Or Cells(D, 5) = "XENTIM" Or Cells(D, 5) = "XEOC" Or Cells(D, 5) = "XESD" Or Cells(D, 5) = "XEWL" Or Cells(D, 5) = "XEXTIM" Or Cells(D, 5) = "XFOPED" Or Cells(D, 5) = "XGA" Or Cells(D, 5) = "XGR" Or Cells(D, 5) = "XGRD" Or Cells(D, 5) = "XGUYP" Or Cells(D, 5) = "XJB" Or Cells(D, 5) = "XJB" Or Cells(D, 5) = "XLP" Or Cells(D, 5) = "XMH" _
Or Cells(D, 5) = "XMHSE" Or Cells(D, 5) = "XMHSE" Or Cells(D, 5) = "XMM" Or Cells(D, 5) = "XMR" Or Cells(D, 5) = "XMRKR" Or Cells(D, 5) = "XMTR" Or Cells(D, 5) = "XPGC" Or Cells(D, 5) = "XPJ" Or Cells(D, 5) = "XPOT" Or Cells(D, 5) = "XPP" Or Cells(D, 5) = "XRISE" Or Cells(D, 5) = "XSEP" Or Cells(D, 5) = "XSHD" Or Cells(D, 5) = "XSIGN" Or Cells(D, 5) = "XSS" Or Cells(D, 5) = "XTANK" _
Or Cells(D, 5) = "XTELEPED" Or Cells(D, 5) = "XTLT" Or Cells(D, 5) = "XTREE" Or Cells(D, 5) = "XTUA" Or Cells(D, 5) = "XVB" Or Cells(D, 5) = "XVENT" Or Cells(D, 5) = "XWF" Or Cells(D, 5) = "XWH" Or Cells(D, 5) = "XWH" Or Cells(D, 5) = "XWINDMILL" Then
Else

Cells(D, 5).Resize(1, 1).Interior.ColorIndex = 50


End If

Next D
End Sub

It is nasty i know but that is all i know how to do.

1 reply

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
762
Try it like this

    bdoit = False
    Select Case Cells(5, "D")
    
        Case Is = "AB", "AP", "APAN", "AT"
            bdoit = True
            
        Case Is = "BARN", "BD", "BERM", "BK", "BLO", "BM", "BOC", "BOLL", "BRE", "BRK"
            bdoit = True
        
        Case Is = "CAN", "CCR", "CFP", "CFR", "CG", "CI", "CK", "CLR", "COMP", "COOL", "CP", "CPI", "CPR", "CRK", "CROPC"
            bdoit = True
        
        ' other CASES go here


        Case Else
            bdoit = False
    
    End Select
    

    If bdoit = True Then
    
    
    Else
    
    End If
Thanks for your help that worked great.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!