Code anomally but can't figure out why - need to figure out the error of the code

[rkol297]

I have this code below that basically what result I want is if column "E" contains one of the following values:

"ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"

Keep the row no matter what:

If column "E" does not contain the above mentioned values then I want to analyze the value of column "I" and if the last two (right sided letters) are -0 keep row if not delete row.

I believe this code below does that however I have an instance where this code failed and deleted a row that contained REMO in column E and the value of column I ended in -4. Based on the values the code should be keeping any row with the value of REMO or the above listed no matter what the value of column I is. Can anyone see any error in the codes logic as to why this would occur?

'REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES 
    Sheets("IG MEDCO").Select
    Dim CelIGMEDCO0F As Range, RngIGMEDCO0F As Range, iIGMEDCO0F As Long
    Set RngIGMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
    For iIGMEDCO0F = RngIGMEDCO0F.Count To 2 Step -1
        Select Case Range("E" & iIGMEDCO0F).Value
            Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
                'do nothing
            Case Else
                If Right(Range("I" & iIGMEDCO0F).Value, 2) <> "-0" _
                Then Rows(iIGMEDCO0F).Delete
            End Select
    Next iIGMEDCO0F
'REMOVES ALL ITEMS EXCEPT VARIABLE FILLS BASED ON THERAPY TYPE IN COLUMN E AND 0 FILLS FOR ALL THERAPIES
    Sheets("PAH MEDCO").Select
    Dim CelPAHMEDCO0F As Range, RngPAHMEDCO0F As Range, iPAHMEDCO0F As Long
    Set RngPAHMEDCO0F = Columns("I").SpecialCells(xlConstants, xlTextValues)
    For iPAHMEDCO0F = RngPAHMEDCO0F.Count To 2 Step -1
        Select Case Range("E" & iPAHMEDCO0F).Value
            Case "ARAL", "BERT", "EPAC", "EPAP", "EPOP", "FL50", "F100", "GLSA", "REMO", "REMP", "RMIP", "RMIV", "VENP", "VENT", "ZEMA", "ZYME", "ZYMF"
                'do nothing
            Case Else
                If Right(Range("I" & iPAHMEDCO0F).Value, 2) <> "-0" _
                Then Rows(iPAHMEDCO0F).Delete
            End Select
    Next iPAHMEDCO0F

 

[VoG]

Your logic is impeccable but maybe you have leading or trailing spaces. Try

Select Case Trim(Range("E" & iPAHMEDCO0F).Value)

 

[Jonmo1]

In addition to Vog's comment, perhaps capitalization is an issue as well..

Select Case Ucase(Trim(Range("E" & iPAHMEDCO0F).Value))

 

[Norie]

Sometimes the problem with this sort of thing is the data rather than the code.

Have you checked the data to see if the row being deleted has the exact text 'REMO' in column E?

No trailing/leading spaces, other extraneous characters etc.

 

[rkol297]

My initial thought was that the report was incorrect so I investigated further. This report runs hourly and for the first 4 reports today this instance of this row was deleted, however, on the 5th run and the remaining runs to this point in the day the row was NOT deleted.???? So confused.

I removed the Ucase from the code but it did not correct the issue either.

Anyone have any further thoughts?

 

[Norie]

Did you check the data?