CODEWITHSUNDEEP
vba
JBear
JBear

Reputation: 45

Activecell not in Array

Having difficulty comparing my activecell against a predefined array.

After importing data from another source I want to trim out the columns I don't need

I've trued the array as both undefined, string and variant And tried activecell / activecell.value <> NeededColumns No joy :(

 Sub Trim()        
    Dim NeededColumns As Variant
        NeededColumns = Array("APPLICATION NUMBER", "DATE", "BRAND", "CHANNEL", "SCHEME")


    Range("A1").Select
    Do Until ActiveCell = ""
        If Application.WorksheetFunction.Match(ActiveCell.Value, NeededColumns, 0) = 0 Then
            ActiveCell.EntireColumn.Delete
        Else
            ActiveCell.Offset(0, 1).Select
        End If
    Loop
End Sub

Upvotes: 2

Views: 179

Answers (1)

Byron Wall
Byron Wall

Reputation: 4010

You have fallen victim to the odd behavior of WorksheetFunction.Match when it cannot find a match. Instead of returning the error, it throws a run time error which gums up the works.

Since the premise of this question is searching for whether or not something is in a list, you are nearly guaranteed to see this issue since you expect to have some items that will not show up. In the formula version of MATCH you would get #N/A when it does not find a match. Here, you get a runtime error. There are two solutions to this:

  1. Use the Application.Match version of the function instead of Application.WorksheetFunction.Match. Unfortunately, Intellisense will not help you here, but it is a valid function. It has the key difference that it will return an error instead of throwing a runtime error. You can then check this error using IsError like your original idea.
  2. Use an On Error Goto XXX to route the error to somewhere instead of the If. You can generally assume that an error here means you did not find an entry, and then work around it that way.

Given these two choices, I strongly recommend you go with option 1. That code would look something like:

Sub Trim()
    Dim NeededColumns As Variant
    NeededColumns = Array("APPLICATION NUMBER", "DATE", "BRAND", "CHANNEL", "SCHEME")


    Range("A1").Select
    Do Until ActiveCell = ""
        If IsError(Application.Match(ActiveCell.Value, NeededColumns, 0)) Then
            ActiveCell.EntireColumn.Delete
        Else
            ActiveCell.Offset(0, 1).Select
        End If
    Loop
End Sub

I have traded your =0 condition for a check on IsError(Application.Match(...)) this will return True if the column is not found.

Additional reading: http://dailydoseofexcel.com/archives/2004/09/24/the-worksheetfunction-method/

Upvotes: 1

Related Questions