Results 1 to 5 of 5

Excel VBA - check if string (cell value) exists in New Collection

This is a discussion on Excel VBA - check if string (cell value) exists in New Collection within the Excel Questions forums, part of the Question Forums category; How can I check if an item exist within a New Collection? I have a routine which put unique cell ...

  1. #1
    Board Regular
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    229

    Default Excel VBA - check if string (cell value) exists in New Collection

    How can I check if an item exist within a New Collection?

    I have a routine which put unique cell values into a new collection if those cells values meet a particular criteria. This works well. I can output the collection values on another worksheet fine.

    The object variable MyRng4 refers to a column of data
    NewColl is the name for my New Colleciton

    Code:
    On Error Resume Next
        For Each MyCell In MyRng4.Cells
        If MyCell.Offset(0, -1).Value = strBrandLimit Then
            NewColl.Add MyCell.Value, CStr(MyCell.Value)
        End If
     Next MyCell
    After I put these items into my collection, I now want to loop back through my range and delete all the rows where the cell value is NOT in the collection.

    I realize I could probably pass my collection into an array and then check to see if the item is within that array - but I feel there must be away to check if a string is in a New Collection, and if there is way I would like to learn that method.

    Any help would be greatly appreciated. Cheers.
    Last edited by Harry Flashman; Aug 29th, 2013 at 05:53 AM.

  2. #2
    Board Regular
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    229

    Default Re: Excel VBA - check if string (cell value) exists in New Collection

    Further to the information above...I had something like this in mind

    Code:
    For Each MyCell In MyRng4.Cells
              chk = NewColl.Item(MyCell)
            ' If item is not there then do something
    Next MyCell
    But I am not sure what to do. I presume I need some kind of error handler.

  3. #3
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    11,827

    Default Re: Excel VBA - check if string (cell value) exists in New Collection

    You could use the AutoFilter feature to filer for rows <> strBrandLimit then delete the filterd rows.

    Code:
        Application.ScreenUpdating = False
        With MyRng4.Offset(, -1)
            .AutoFilter 1, "<>" & strBrandLimit
            .Offset(1).Resize(.Count - 1).EntireRow.Delete
            .Parent.AutoFilterMode = False
        End With
        Application.ScreenUpdating = True
    Alternatively, you could use a Dictionary object. It's a special collection with an .Exists method.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Please surround your pasted VBA code with CODE tags e.g.;
    [CODE] your VBA code here [/CODE]
    The pound # icon in the forum editor will apply the CODE tags around your selected text.

  4. #4
    Board Regular
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    229

    Default Re: Excel VBA - check if string (cell value) exists in New Collection

    Quote Originally Posted by AlphaFrog View Post
    You could use the AutoFilter feature to filer for rows <> strBrandLimit then delete the filterd rows.

    I am at home now and unfortunately I don't have a working version of excel on my home computer (which has decided it does not like Office 2013 anymore but that is another story).
    I think the filter method might not work (although I will conduct experiments tomorrow).

    I don't actually want to delete all rows that do not contain the value strBrandLimit (column A).
    Here is an basic idea of how the data is structured:

    Column A is a series of Questions
    Column B is a series of Brands (and some other entities)
    Column B is not a unique list. The brands are repeated for each question (but not all brands are repeated for each question).

    The purpose of the New Collection is to extract a unique list of brands.
    I want to delete all rows that contain brands that are not in that list (or at least I want the option to do something like that).

    I could put the unique list in a named range on another worksheet, and perhaps use a formula to determine which brands are not in the list, and then delete those rows. But I thought there would be someway which involves whether the brands exist within the collection.

    I will investigate the Dictionary object. That .Exist property method sounds like it might be what I need. Thanks.

    If anyone else has any suggestion regarding checking to see if an item exists with a collection I would like to hear. My question is not just about solving this specific problem, but learning how to work with Collections in general.
    Last edited by Harry Flashman; Aug 29th, 2013 at 08:06 AM.

  5. #5
    Board Regular
    Join Date
    May 2011
    Location
    Melbourne, Australia
    Posts
    229

    Default Re: Excel VBA - check if string (cell value) exists in New Collection

    I am still open to suggestions about how to solve my problem, which I think I have not explained very well.
    Here is a simplified example of the data I am working with:

    Criteria
    Name
    Criteria 1 Mary
    Criteria 1 Pat
    Criteria 1 Sam
    Criteria 1 Andy
    Criteria 2 Bob
    Criteria 2 Zack
    Criteria 2 Zack
    Criteria 2 Kyle
    Criteria 2 Andy
    Criteria 2 Andy
    Criteria 3 Pat
    Criteria 3 Bob
    Criteria 3 Jane
    Criteria 3 Pat
    Criteria 4 Bob
    Criteria 4 Bob
    Criteria 4 Kyle
    Criteria 4 Jane
    Criteria 4 Bob
    Criteria 5 Bob
    Criteria 5 Sam
    Criteria 5 Kyle

    Neither column A or B contains unique values
    I want to extract a unique list of names from column B that meet the criteria in column A
    I then want to loop through each cell in column B and IF the name is in my unique list that I have just created do something to that row.

    This is the code I have come up with:

    Code:
    Sub CheckIfStringIsInCollection()
    
    Dim ws As Worksheet
    Dim MyRng As Range
    Dim MyRng2 As Range ' My range minus the header
    Dim MyRngC As Range ' a single column within MyRng
    Dim MyCell As Range
    Dim MyArray()
    Dim i As Long
    
    Dim UniqueColl As New Collection
    Dim UniqueVal As Variant
    
    Set ws = ActiveSheet
    Set MyRng = ws.Range("A1").CurrentRegion
    Set MyRng2 = MyRng.Resize(MyRng.Rows.Count - 1).Offset(1, 0)
    Set MyRngC = MyRng2.Columns(2)
    
    On Error Resume Next
    For Each MyCell In MyRngC.Cells
        If MyCell.Offset(0, -1).Value = "Criteria 2" Then
            UniqueColl.Add MyCell.Value, CStr(MyCell.Value)
        End If
    Next MyCell
    On Error GoTo 0
    
    For Each UniqueVal In UniqueColl
            ReDim Preserve MyArray(i)
            MyArray(i) = UniqueVal
            i = i + 1
    Next UniqueVal
    
    For Each MyCell In MyRng2.Cells
        If IsInArray(MyCell.Value, MyArray) Then
            MyCell.EntireRow.Font.Bold = True
        Else
            MyCell.EntireRow.Font.Bold = False
      End If
    Next MyCell
    
    End Sub
    
    Function IsInArray(stringToBeFound As String, arr As Variant) As Boolean
      IsInArray = (UBound(Filter(arr, stringToBeFound)) > -1)
    End Function
    The code uses New Collection to extract a unique list
    Each item is then passed to an array.
    I then use a Function IsInArray to check which cells contain a name that is in the array.
    For cells where the name is in the array the entire row is then bolded.

    It works pretty well. So I guess I have solved my own problem. But if there is a better method I would love to hear about it.
    I am still yet to figure out how to use the Dictionary Object - so if someone can give me some tips there I am all ears. Cheers.

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com