none
How to loop through array and clear cells not matching whitelist? RRS feed

  • Question

  • Hi,

    I'm struggeling with a range of currencies in Excel 2010.

    I have an range with currency codes listed in a loadtable. I would like to empty cells in the range that has a currency code that isn't found in my list of valid currency codes.

    Below is my my attempt. I get "Run-time error '424': Object required"

        'Delete unwanted Currencies
        Dim CurrAry As Variant
        Dim ValidCurrAry As Variant
       
        CurrAry = Sheets("LoadTbl").Range("A2:B" & MyLastRow)       'data set that needs to be checked
        ValidCurrAry = Sheets("Start").Range("C7:C55")              'list of valid currencies
        For Each c In CurrAry                                       'Loop through all cells in the data set
            If UBound(Filter(CurrAry.Value, c.Value)) = -1 Then     'Problem - Attempt to use Array and Filter to see if cell value is in list of valid currencies
                c.Value = ""                                        'blank cell if value = -1 (should be indication that currency code is not in valid currency list
            End If
        Next

    Any suggestions are warmly welcome. It's quite possible that I'm going at it the completely wrong way.

    Chr

    Saturday, January 23, 2016 12:15 PM

Answers

  • 1) In your code, c is a value, not a cell, so you cannot use it to set the value of a cell.
    2) Your arrays are two-dimensional. Filter only works with one-dimensional arrays.

    Here is an alternative version:

        Dim Curr As Range
        Dim ValidCurr As Range

        Set Curr = Sheets("LoadTbl").Range("A2:B" & MyLastRow)
        Set ValidCurr = Sheets("Start").Range("C7:C55")
        For Each c In Curr
            If ValidCurr.Find(What:=c.Value, LookAt:=xlWhole) Is Nothing Then
                c.ClearContents
            End If
        Next c

    I assumed that you have declared MyLastRow and c elsewhere, and assigned a value to MyLastRow.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by christian-s Saturday, January 23, 2016 12:59 PM
    Saturday, January 23, 2016 12:49 PM

All replies

  • 1) In your code, c is a value, not a cell, so you cannot use it to set the value of a cell.
    2) Your arrays are two-dimensional. Filter only works with one-dimensional arrays.

    Here is an alternative version:

        Dim Curr As Range
        Dim ValidCurr As Range

        Set Curr = Sheets("LoadTbl").Range("A2:B" & MyLastRow)
        Set ValidCurr = Sheets("Start").Range("C7:C55")
        For Each c In Curr
            If ValidCurr.Find(What:=c.Value, LookAt:=xlWhole) Is Nothing Then
                c.ClearContents
            End If
        Next c

    I assumed that you have declared MyLastRow and c elsewhere, and assigned a value to MyLastRow.


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by christian-s Saturday, January 23, 2016 12:59 PM
    Saturday, January 23, 2016 12:49 PM
  • Hans, thanks for the code and the explanation.

    Code works beautifully. And you're right I have MyLastRow defined and assigned up higher in the code.

    Br, Chr

    Saturday, January 23, 2016 1:02 PM