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,

 

[Rick Rothstein]

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.

I am not sure why it should be "a lot slower". While Like is not super fast, it surprises me that you can note any slowdown. As for the missing "C"... I do not see how that can possibly happen if you are using the code exactly as I posted it.

 

[johnnyL]

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.

My version with the corrected line for mid to left works on my end, are you sure you replaced the correct line? Post the code that you have and maybe we can see what the issue may be.

 

[johnnyL]

I am not sure why it should be "a lot slower". While Like is not super fast, it surprises me that you can note any slowdown.

I am not sure why either, but it is definitely noticeably slower. I think I'll put a timer on it to make sure my eyes aren't playing tricks on me.

 

[Livin404]

I am not sure why it should be "a lot slower". While Like is not super fast, it surprises me that you can note any slowdown. As for the missing "C"... I do not see how that can possibly happen if you are using the code exactly as I posted it.

Here is the code as I have it. It is slightly different but exactly the same concept. In this case the letter U is getting cut off. Yes unfortunately it is causing the run the VBA at a much slower speed. Thank you,

Sub Unit_Training_MSN()
'
    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 "[ACEFGHIKLMNPQRSW0124678][ESU][N]*" 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("UNIT/TRNG MSN", 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 "UNIT/TRNG MSN" or "/UNIT/TRNG MSN".
'                                                                                   ' 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 "/UNIT/TRNG MSN" 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

 

[Rick Rothstein]

My code was built on your having to display either "CHANNEL" or "/CHANNEL"... you changed that completely so I am not sure why you would expect to still work. So that we can help you, tell us how the text "UNIT/TRNG MSN" is to be shown when the cell in Column G is empty and when that cell has some text in it?

 

[johnnyL]

Ok, timings are in and it is confirmed, my eyes were not playing tricks on me.

 

[Livin404]

My code was built on your having to display either "CHANNEL" or "/CHANNEL"... you changed that completely so I am not sure why you would expect to still work. So that we can help you, tell us how the text "UNIT/TRNG MSN" is to be shown when the cell in Column G is empty and when that cell has some text in it?

Actually it should be "UNIT TRNG MSN" if the cell is empty and "/UNIT TRNG MSN" if text already exists. Sorry about the confusion. Thank you,

 

[Rick Rothstein]

Actually it should be "UNIT TRNG MSN" if the cell is empty and "/UNIT TRNG MSN" if text already exists. Sorry about the confusion. Thank you,

In that case, as long as there is a leading slash, just put the "/UNIT TRNG MSN" in the Mid function in place of the "/CHANNEL" and it should work fine. However, I note johnnyL's time trials and it would seem using the Like operator is much slower than I thought it would be. The Like operator gives enormous flexibility but apparently as a larger cost than I thought.

 

[Livin404]

My version with the corrected line for mid to left works on my end, are you sure you replaced the correct line? Post the code that you have and maybe we can see what the issue may be.

Here is a my the VBA here is what an example of a alpha numeric code I like to break down is AUN08TA06216

Sub Unit_Training_MSN()

    Dim FirstCharacterList As String
    Dim SecondCharacterList As String
    Dim ThirdCharacterList  As String

    FirstCharacterList = "A,C,E,F,G,H,I,K,L,M,N,P,Q,R,S,W,0,1,2,4,6,7,8"
    SecondCharacterList = "E,S,U"
    ThirdCharacterList = "N"

    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
         If InStr(FirstCharacterList, Left(Range("A" & i), 1) & ",") <> 0 Then                    ' If 1st character Found in FirstCharacterList Then
            If InStr(SecondCharacterList, Mid(Range("A" & i), 2, 1) & ",") <> 0 Then            '   If 2nd character Found in SecondCharacterList Then
                If InStr(ThirdCharacterList, Mid(Range("A" & i), 3, 1) & ",") <> 0 Then         '       If 3rd character Found in ThirdCharacterList Then
                    If ActiveSheet.Range("G" & i) = "" Then                                     '           If the accompanying G column cell is blank Then
                        ActiveSheet.Range("G" & i) = "UNIT TRNG MSN"                                  'Make the cell = to "UNIT TRNG MSN"
                    Else                                                                                           '           Else
                        ActiveSheet.Range("G" & i) = ActiveSheet.Range("G" & i) & "/UNIT TRNG MSN"    'Append "/UNIT TRNG MSN" to what is already in the G column cell
                    End If
                End If
            End If
        End If
    Next
End Sub

Thank you,

 

[Livin404]

I am not sure why either, but it is definitely noticeably slower. I think I'll put a timer on it to make sure my eyes aren't playing tricks on me.

I figure it out, it is because I had no comma after the last character "SecondCharacterList = "E,S,U". Now I do and it makes all the difference.