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
oops you are correct, I forgot to change that to Left.

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

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

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Rick Rothstein

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

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
@Rick Rothstein, your coding is, by far, more genius than mine. What else is new? :rolleyes:

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
I think you would get much more love, if that is even possible , if you commented your codes

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

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

@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:

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

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

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

ADVERTISEMENT

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

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

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

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

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

Forum statistics

Threads
1,151,700
Messages
5,766,007
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