up vote 0 down vote favorite
share [fb]

I'm looking for some guidance with a task I have been set from my eager new boss!

I've got a list of error codes in a named range "List", and a list of specific error codes that need to be identified "Codes".

What I need is some VBA code that will will check "List" and if any code exists that isn't in the "Codes" list it will delete it. (So, if it's in the "codes" range it stays, otherwise it gets deleted).

Can anybody help me with this please?

So far I've got this code, but it only does the opposite and deletes the codes I want to keep!

Sub DeleteCodes()
  Application.ScreenUpdating = False
  Dim InRange As Range, CritRange As Range
  Dim InCell As Range, CritCell As Range

  Set InRange = Range("Data")           ' all selected source cells
  Set CritRange = Range("List")         ' the named range of words to be excluded

  For Each InCell In InRange.Cells
    For Each CritCell In CritRange.Cells
      If InCell = CritCell Then
        InCell = ""                     ' blank it
        Exit For                        ' exit inner for
      End If

    Next CritCell
  Next InCell

  Application.ScreenUpdating = True
End Sub
link|improve this question
Please kindly show what you've done so far. Thank you. – Adam Bernier Jun 14 '11 at 17:13
1  
Hi Adam. I've edited the question with current code which unfortunately only does the opposite of what I'm after! – tonypreece Jun 14 '11 at 17:20
feedback

2 Answers

up vote 2 down vote accepted 
   Sub DeleteCodes()

        Dim InRange As Range, InCell As Range
        Dim CritRange As Range
        Dim v, f As Range

        Set InRange = Range("Data")   ' all selected source cells
        Set CritRange = Range("List") ' the named range of words to be excluded

        Application.ScreenUpdating = False
        For Each InCell In InRange.Cells
            Set f = CritRange.Find(InCell.Value, , xlValues, xlWhole)
            If f Is Nothing Then InCell.Value = ""
        Next InCell
        Application.ScreenUpdating = True
    End Sub
link|improve this answer
feedback
up vote 1 down vote

Try:

Sub DeleteCodes()
  Dim rCell As Range

  Application.ScreenUpdating = False

  For Each rCell In [List].Cells
      If Application.WorksheetFunction.CountIf([Codes], rCell.Value) = 0 Then
        rCell.Value = ""
  Next rCell

  Application.ScreenUpdating = True
End Sub
link|improve this answer
@Lance, are you sure thats a correct edit? I interpreted the question as Tim did and based on the OP's code. – Reafidy Jun 15 '11 at 0:36
@Readfidy, yes, as per his instructions, not his code. His code is messed up and Tim took his cue from that. See the 3rd paragraph. (If you want to edit it back, go ahead, I've no dog in this hunt). – Lance Roberts Jun 15 '11 at 6:16
Okay. Will leave as is, thanks. – Reafidy Jun 15 '11 at 6:30
feedback

Your Answer

 
or
required, but never shown

Not the answer you're looking for? Browse other questions tagged or ask your own question.