VBA for Wildcards with Array

[Livin404]

Greetings, I am able to insert text in one cells based on the value of another cell. With help we got it to work with Wildcards. Now that I am search for a code were a wildcard in my case two wildcard position can be a number of Multiple characters. I believe I'm going to need something like an array.

My code I know will be something similar to:

 Sub CHANNEL_MSN()
  Dim i As Long
  For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
    If Left(Cells(i, 1).Value, 2) Like "***" And Left(Cells(i, 1).Value, 2) = Array("F", "M", "P", "J", "V", "L") Then
  If Left(Cells(i, 1).Value, 3) Like "***" And Left(Cells(i, 1).Value, 3) = Array("X", "J", "R", "U", "W", "Y", "Z", "E", "Q", "F") Then
 If ActiveSheet.Range("G" & i) = "" Then
     ActiveSheet.Range("G" & i) = "CHANNEL"
    Else
     ActiveSheet.Range("G" & i) = ActiveSheet.Range("G" & i) & "/CHANNEL"
    End If
   End If
  Next i
End Sub

I know the array portion needs attention. The first character can be anything, it is the 2 and 3 character I need to be limited to the options I identified.

Thank you,

 

[johnnyL]

oops you are correct, I forgot to change that to Left.

Change:

        If InStr(FirstCharacterList, Mid(Range("A" & i), 1, 1) & ",") <> 0 Then                 ' If 1st character Found in FirstCharacterList Then

To:

        If InStr(FirstCharacterList, Left(Range("A" & i), 1) & ",") <> 0 Then                 ' If 1st character Found in FirstCharacterList Then

 

[Rick Rothstein]

Given you made no comment about it, can I assume you did not even try the code I posted earlier in Message #16? Just so you know, if you want to test for specific first characters, all you would have had to do with my code is replace the question mark with square brackets containing the characters you want to test that position for. I'll post the code with that change (I'll make up characters, shown in red, for the test which you would need to replace with your actual characters)...

Sub CHANNEL_MSNV3()
  Dim R As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A")).Value
  Result = Range("G1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "[AKNS][FJLMPV][EFJQRUWXYZ]*" Then
      Result(R, 1) = Result(R, 1) & Mid("/CHANNEL", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("G1").Resize(UBound(Result)) = Result
End Sub

 

[johnnyL]

@Rick Rothstein, your coding is, by far, more genius than mine. What else is new?

I think you would get much more love, if that is even possible , if you commented your codes so the OP, and others, could further appreciate your enormous skills in excel. If I look through your code, slowly I might add, I can see what your code is doing, most of the time. Others may not. I realize you are certainly under no obligation to put forth the additional effort needed to throw in comments, but speaking only for myself, I would love to see more people comment their coding so it can be appreciated even more than what it already is.

 

[Rick Rothstein]

I think you would get much more love, if that is even possible , if you commented your codes

Sub CHANNEL_MSNV3()
  Dim R As Long, Data As Variant, Result As Variant
 
  ' Assign all values in Column A to an array
  ' (It is faster to work with an array in memory
  ' than directly with the cells on a worksheet
  Data = Range("A1", Cells(Rows.Count, "A")).Value
 
  ' Assign the output Column G to an array. We will change
  ' only those values in it meeting the stated conditions
  Result = Range("G1").Resize(UBound(Data)).Value
 
  ' Loop through the array containing the data to
  ' test each value for the stated conditions
  For R = 1 To UBound(Data)
 
    ' This test uses the Like operator. It has a lot more
    ' functionality than I make use of here, but for this
    ' test, a single set of square brackets stands for a
    ' single character and that character is allowed to be
    ' any one of the list of characters within it.
    If Data(R, 1) Like "[AKNS][FJLMPV][EFJQRUWXYZ]*" Then
   
      ' If the data element meets the above test, then the
      ' value in the ouput array is modified. We take whatever
      ' it is and concatenate either "CHANNEL" or "/CHANNEL".
      ' Which is controlled by this logical expression...
      ' (Result(R, 1) = ""). If True, it will evaluate to -1,
      ' if False to 0. So we use the Mid function on the text
      ' string "/CHANNEL" starting at the first position if
      ' the logical expression was False (1 plus 0 is still 1)
      ' or the second position if the logical expression is
      ' True (minus a minus one is plus one so when added to
      ' the hard-coded 1 becomes 2)
      Result(R, 1) = Result(R, 1) & Mid("/CHANNEL", 1 - (Result(R, 1) = ""))
    End If
  Next
 
  ' Assign the output array with is modified values
  ' back to the original range it came from
  Range("G1").Resize(UBound(Result)) = Result
End Sub

 

[johnnyL]

@Rick Rothstein That is an excellent addition for commenting code. Possibly overkill lol.

If I could suggest one more suggestion, Make it left to right format instead of top to bottom.

Have the code on the left side, and the comments on the right side.

That would shorten up the length of the code and make it more friendly to people that just want to view the code, via the left side of the screen, and if needed, view the comments on the right side of the screen.

An example of your commented code that you just posted, in a left to right format could be:

Sub CHANNEL_MSNV3ReformattedForLeftToRightCodeComments()
'
    Dim R As Long, Data As Variant, Result As Variant
'
    Data = Range("A1", Cells(Rows.Count, "A")).Value            ' Assign all values in Column A to an array (It is faster to work with
'                                                               ' an array in memory than directly with the cells on a worksheet)
'
    Result = Range("G1").Resize(UBound(Data)).Value             ' Assign the output Column G to an array. We will change only those
'                                                               ' values in it meeting the stated conditions
'
    For R = 1 To UBound(Data)                                   ' Loop through the array containing the data to test each value for the stated conditions
'
        If Data(R, 1) Like "[AKNS][FJLMPV][EFJQRUWXYZ]*" Then   ' This test uses the Like operator. It has a lot more functionality than I
'                                                               ' make use of here, but for this test, a single set of square brackets stands
'                                                               ' for a single character and that character is allowed to be any one of the
'                                                               ' list of characters within it.
'
            Result(R, 1) = Result(R, 1) & Mid("/CHANNEL", 1 - (Result(R, 1) = ""))  ' If the data element meets the above test, then the
'                                                                                   ' value in the ouput array is modified. We take whatever
'                                                                                   ' it is and concatenate either "CHANNEL" or "/CHANNEL".
'                                                                                   ' Which is controlled by this logical expression...
'                                                                                   ' (Result(R, 1) = ""). If True, it will evaluate to -1,
'                                                                                   ' if False to 0. So we use the Mid function on the text
'                                                                                   ' string "/CHANNEL" starting at the first position if
'                                                                                   ' the logical expression was False (1 plus 0 is still 1)
'                                                                                   ' or the second position if the logical expression is
'                                                                                   ' True (minus a minus one is plus one so when added to
'                                                                                   ' the hard-coded 1 becomes 2)
        End If
    Next
'
    Range("G1").Resize(UBound(Result)) = Result                 ' Assign the output array with is modified values back to the original range it came from
End Sub

Same code and comments, but less like reading through a novel, and like I said shorter code footprint,

 

[Livin404]

If InStr(FirstCharacterList, Left(Range("A" & i), 1) & ",") <> 0 Then

I did try the left and I'm afraid it is still not working. I truly appreciate your help. Sorry it's taking me a while to respond. I don't get a lot of time when I'm not at work. I'll continue to work on it, I'm confident the answer is very near.

 

[Livin404]

Sub CHANNEL_MSNV3()
  Dim R As Long, Data As Variant, Result As Variant
 
  ' Assign all values in Column A to an array
  ' (It is faster to work with an array in memory
  ' than directly with the cells on a worksheet
  Data = Range("A1", Cells(Rows.Count, "A")).Value
 
  ' Assign the output Column G to an array. We will change
  ' only those values in it meeting the stated conditions
  Result = Range("G1").Resize(UBound(Data)).Value
 
  ' Loop through the array containing the data to
  ' test each value for the stated conditions
  For R = 1 To UBound(Data)
 
    ' This test uses the Like operator. It has a lot more
    ' functionality than I make use of here, but for this
    ' test, a single set of square brackets stands for a
    ' single character and that character is allowed to be
    ' any one of the list of characters within it.
    If Data(R, 1) Like "[AKNS][FJLMPV][EFJQRUWXYZ]*" Then
  
      ' If the data element meets the above test, then the
      ' value in the ouput array is modified. We take whatever
      ' it is and concatenate either "CHANNEL" or "/CHANNEL".
      ' Which is controlled by this logical expression...
      ' (Result(R, 1) = ""). If True, it will evaluate to -1,
      ' if False to 0. So we use the Mid function on the text
      ' string "/CHANNEL" starting at the first position if
      ' the logical expression was False (1 plus 0 is still 1)
      ' or the second position if the logical expression is
      ' True (minus a minus one is plus one so when added to
      ' the hard-coded 1 becomes 2)
      Result(R, 1) = Result(R, 1) & Mid("/CHANNEL", 1 - (Result(R, 1) = ""))
    End If
  Next
 
  ' Assign the output array with is modified values
  ' back to the original range it came from
  Range("G1").Resize(UBound(Result)) = Result
End Sub

I will definitely be working on your solution, too. You have helped me out before. I assure you I'm not ignoring your response. Thank you for your patience.

 

[Rick Rothstein]

@Rick RothsteinIf I could suggest one more suggestion, Make it left to right format instead of top to bottom.

I have been programming for nearly 40 years now and when I comment code, it is always above the line it refers to. While I understand your suggestion, it is way to different than what I am used to so I would find it too awkward to do it that way. Besides, if you have followed any code I have written in the past, you will remember that some of my code lines can get quite long... commenting the way you suggest would put the comments way off the page. Rather than me spending the time trying to implement your suggested commenting style, for those procedures where I do provide comments, if someone wanted to see the code more clearly, they can spend the time and copy it, then delete the comments from the copy... that way, they would have a commented and uncommented set of procedures to work from.

 

[johnnyL]

Old dog, no new tricks, Got it. I just was offering a suggestion.

Just because you have been doing it that way since the eon of time beginnings, doesn't ... aww forget it. Thank you again @Rick Rothstein for all of your contributions.

 

[Livin404]

I have been programming for nearly 40 years now and when I comment code, it is always above the line it refers to. While I understand your suggestion, it is way to different than what I am used to so I would find it too awkward to do it that way. Besides, if you have followed any code I have written in the past, you will remember that some of my code lines can get quite long... commenting the way you suggest would put the comments way off the page. Rather than me spending the time trying to implement your suggested commenting style, for those procedures where I do provide comments, if someone wanted to see the code more clearly, they can spend the time and copy it, then delete the comments from the copy... that way, they would have a commented and uncommented set of procedures to work from.

Thank you I did use your VBA code and it gets me a lot closer. It is reading the three letters as we hoped. However, for some reason it is dropping the first letter in the response. I'm getting "HANNEL" rather than "CHANNEL". Additionally, the VBA does seem to run a lot slower than it had before. But at least I'm getting words in the desired column now.