VBA for Wildcards with Array

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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:

VBA Code:
 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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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,

VBA Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Ok, timings are in and it is confirmed, my eyes were not playing tricks on me.

TimeTest.PNG
 

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

VBA Code:
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

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,699
Messages
5,766,005
Members
425,322
Latest member
galaxy6623top

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top