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,
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
Yep, all three of the lists have to end in a comma, otherwise the last character in the lists will not be found as a match.
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Ok, timings are in and it is confirmed, my eyes were not playing tricks on me.

View attachment 44076
Through a stupid mistake in my code, I had it processing the cells in all 1,000,000+ rows on a worksheet instead of just the actual data cells themselves. Try your timing test on the following corrected code (I have highlighted in red my original omission)... it should be much faster.
Rich (BB code):
Sub CHANNEL_MSNV3()
  Dim R As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  Result = Range("G1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "[ACEFGHIKLMNPQRSW0124678][ESU]N*" Then
      Result(R, 1) = Result(R, 1) & Mid("/UNIT TRNG MSN", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("G1").Resize(UBound(Result)) = Result
End Sub
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@Rick Rothstein I think you broke my timer. :)

TimeTest2.PNG
 

Livin404

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

ADVERTISEMENT

Through a stupid mistake in my code, I had it processing the cells in all 1,000,000+ rows on a worksheet instead of just the actual data cells themselves. Try your timing test on the following corrected code (I have highlighted in red my original omission)... it should be much faster.
Rich (BB code):
Sub CHANNEL_MSNV3()
  Dim R As Long, Data As Variant, Result As Variant
  Data = Range("A1", Cells(Rows.Count, "A").End(xlUp)).Value
  Result = Range("G1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "[ACEFGHIKLMNPQRSW0124678][ESU]N*" Then
      Result(R, 1) = Result(R, 1) & Mid("/UNIT TRNG MSN", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("G1").Resize(UBound(Result)) = Result
End Sub
Thank you for your support. I haven't logged on the last few days as I had a bit of a crises. I will give yours a go. I was wondering something if I wanted to show the 3rd and 4 characters as a pair how I would I show that within the [ ]? Thank you,
 

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you wanted to check to see that all three of the first three characters matchyou could create another list for the first character and then you would need to add the If checking code to check for it. Something like following:

VBA Code:
Sub CHANNEL_MSNV3Characters()
'
    Dim FirstCharacterList  As String
    Dim SecondCharacterList As String
    Dim ThirdCharacterList  As String
'
     FirstCharacterList = "A,B,C,D,E,F,G,H,"
    SecondCharacterList = "F,M,P,J,V,L,"
     ThirdCharacterList = "X,J,R,U,W,Y,Z,E,Q,F,"
'
    For i = 1 To Range("A" & Rows.Count).End(xlUp).Row
        If InStr(FirstCharacterList, Mid(Range("A" & i), 1, 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) = "CHANNEL"                                  '               Make the cell = to "CHANNEL"
                    Else                                                                        '           Else
                        ActiveSheet.Range("G" & i) = ActiveSheet.Range("G" & i) & "/CHANNEL"    '               Append "/CHANNEL" to what is already in the G column cell
                    End If
                End If
            End If
        End If
    Next
End Sub
Hey I've been out for a day, and things are looking a bit positive. I was wondering something what if I wanted to show the 3rd and 4 characters as a pair how I would I write that? I tried thirdandfourthcharacterlist= "01,02,05," and I know that does not work. Thank you,
 

Rick Rothstein

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

ADVERTISEMENT

I am not sure what you mean when you say you want "to show the 3rd and 4th characters as a pair". Can you describe in more detail what you mean by that?
 

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
If you want to check for the 4th character via @Rick Rothstein code then you could replace the following:

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

With something like the following:

VBA Code:
'
        If Data(R, 1) Like "[ACEFGHIKLMNPQRSW0124678][ESU][N][ABC]*" 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.

That would add a 4th character option. Is that what you were asking?
 

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
The codes I’m deciphering are 12 digits alphanumeric. In most cases the digits act singularly. However there are occasions where Two digits will be used as a pair. What I’m using now works great, but there are times where the 3rd and 4th digits need to be used together. Digits one and two would be used as before, but a third line where digits 3 & 4 will be used to decipher the code. Thank you
 

Livin404

Well-known Member
Joined
Jan 7, 2019
Messages
502
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
If you want to check for the 4th character via @Rick Rothstein code then you could replace the following:

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

With something like the following:

VBA Code:
'
        If Data(R, 1) Like "[ACEFGHIKLMNPQRSW0124678][ESU][N][ABC]*" 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.

That would add a 4th character option. Is that what you were asking?
Hey thank you, the code works great I am working on changing them over to your style. I have a few things I'm still working on, naturally I have a few issues I'm trying to resolved. In the mean time I posted a new question that can address a specific concern. Thank you,
 
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