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]

Perhaps some wildcard information may assist you.

An asterisk * equates to any number of characters. Multiple asterisks are redundant.

*5 would find 15, A5, 2465, etc. The asterisk basically means 'anything' found. You could have *5* which would find anything with a 5 in it. You could also have 5*.

A question mark as a wildcard equates to only one character. So 3?5 would find 3A5, 345, 3-5, etc.

If you want to look for 2 or more characters, that can be anything, that is when you would add multiple question marks ie. 1???5 would find 12345, 1A3B5, etc.

Hopefully that helps you,

one asterisk covers 1 or more characters
each question mark covers a single character

 

[Livin404]

Perhaps some wildcard information may assist you.

An asterisk * equates to any number of characters. Multiple asterisks are redundant.

*5 would find 15, A5, 2465, etc. The asterisk basically means 'anything' found. You could have *5* which would find anything with a 5 in it. You could also have 5*.

A question mark as a wildcard equates to only one character. So 3?5 would find 3A5, 345, 3-5, etc.

If you want to look for 2 or more characters, that can be anything, that is when you would add multiple question marks ie. 1???5 would find 12345, 1A3B5, etc.

Hopefully that helps you,

one asterisk covers 1 or more characters
each question mark covers a single character

Yes you're, it would be more appropriate to use the ?. So the only think I need to do with my formula is to change the * with a ??. Thank you for the response.

 

[johnnyL]

I think it will take more than that, but please let us know how you do and if you need further assistance

 

[Livin404]

Oh yes for sure, I tried using the formula I post but with ? instead of *. It just errored out.

 

[johnnyL]

Shocker!

Which error did you get in your endeavor?

 

[Livin404]

Shocker!

here is an image. Thank you,

Which error did you get in your endeavor?

 

[johnnyL]

Well that is dumb Excel for you. You are actually missing an 'End If'

You have three 'If/Then' and only two 'End If'

 

[Rick Rothstein]

Can you explain in clear words exactly what the code you posted in Message #1 is supposed to be parsing?

 

[johnnyL]

@Livin404 If I understand what your code and comments are attempting to do:


You want to check the cells in column A.

You don't care what the first character is, you check to see if the second character is "F", or "M", or "P", or "J", or "V", or "L") and if the second character is any of those values

You then want to check the third character to see if is "X", or "J", or "R", or "U", or "W", or "Y", or "Z", or "E", or "Q", or "F" If that is true also

Then you want to see if the cell in the G column is blank, if it is then set the cell in the G column to "CHANNEL", if it is not blank then append "/CHANNEL" to its existing value.

Otherwise, start checking the next cell in the A Column, continue down the A column until all cells have been checked.


If all of that is correct then the following code should do that:

Sub CHANNEL_MSNV2()
'
    Dim SecondCharacterList As String
    Dim ThirdCharacterList  As String
'
    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(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
    Next
End Sub

It uses a similar approach, but should be a bit less confusing. BTW, you normally won't need to mix wildcards if you are looking for a specific match to something as far as specific characters.