dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] Excel 2010 VBA Using Like On a List of Values

  1. #1

    Thread Starter
    Junior Member skywriter's Avatar
    Join Date
    Feb 2015
    Posts
    16

    Resolved [RESOLVED] Excel 2010 VBA Using Like On a List of Values

    So I am writing some code and I want to check if a cell has one of these list of values in it.

    {"EO";"EI";"PU";"UA";"V";"FML";"STD";"WC";"J";"S";"B";"OL"}

    I thought maybe I could use the like operator, but I've changed around the syntax, removing the curly braces, trading the semi colons for commas removing the quotes, and putting in [], etc etc. So now I need to know if I just need some help with the syntax, or I need to go about this a different way.

    So if any of these values is in a cell I want a true otherwise false.

    Thanks

  2. #2
    Member pike's Avatar
    Join Date
    Jul 2008
    Location
    Alstonville, Australia
    Posts
    52

    Re: Excel 2010 VBA Using Like On a List of Values

    Hi Skywriter
    something like ...
    Code:
    Sub LetterPattern()
      Dim x As Long, z As Long, nRow As Long
      For nRow = 1 To Cells(Rows.Count, "A").End(xlUp).Row
    '  {"EO-EI-PU-UA-V-FML-STD-WC-J-S-B-OL"}
         If "-EO-EI-PU-UA-V-FML-STD-WC-J-S-B-OL-" Like "*-" & Cells(nRow, 1).Value & "-*" Then
         MsgBox "True Row " & nRow
         End If
      Next
    End Sub

  3. #3

    Thread Starter
    Junior Member skywriter's Avatar
    Join Date
    Feb 2015
    Posts
    16

    Re: Excel 2010 VBA Using Like On a List of Values

    Oh man, that worked perfectly I was thinking cell.value like, and you went the other way. Brilliant. In testing it picked up all of those values and bypassed all the ones not in the list.
    Thanks a million.

    Here's how the code fit into my actual code. I'm adding the cell values that match into an array.
    Code:
    If "-EO-EI-PU-UA-FML-STD-WC-J-S-B-OL-" Like "*-" & rngEmp.Cells(rowEmp, colEmp).Value & "-*" Then _
    arrOther(rowEmp, colEmp) = arrOther(rowEmp, colEmp) & empName & " - " & rngEmp.Cells(rowEmp, colEmp).Value & vbNewLine
    Thanks again!!!

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width