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,

 

[Livin404]

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

I have a text string of 12 characters where I'm only concerned with the 2nd and third characters. The second character can have any of the characters in the array I described and the same thing for the third. If any of those conditions are met then word Channel will appear. They both circumstances have to occur in order for the rule to apply. Of course there could already be text in Column G which is why I have the "else" statement. Thank you for asking.

 

[Peter_SSs]

Here is another option to try.

Sub CheckPos2AND3()
  Dim RX As Object
  Dim a As Variant, g As Variant
  Dim i As Long
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Pattern = "^.[FMPJVL][XJRUWYZEQF]"
  a = Range("A1", Range("A" & Rows.Count).End(xlUp)).Value2
  g = Range("G1").Resize(UBound(a)).Value2
  For i = 1 To UBound(a)
    If RX.Test(a(i, 1)) Then g(i, 1) = g(i, 1) & IIf(IsEmpty(g(i, 1)), "", "/") & "CHANNEL"
  Next i
  Range("G1").Resize(UBound(g)).Value = g
End Sub

 

[johnnyL]

I have a text string of 12 characters where I'm only concerned with the 2nd and third characters. The second character can have any of the characters in the array I described and the same thing for the third. If any of those conditions are met then word Channel will appear. They both circumstances have to occur in order for the rule to apply. Of course there could already be text in Column G which is why I have the "else" statement. Thank you for asking.

Man, that sounds very familiar to post #10 in this thread. Did you see/try it?

 

[Livin404]

I performed a couple of tests and everything appears to be working just like I hoped. Thank you so much!

 

[Peter_SSs]

I performed a couple of tests and everything appears to be working just like I hoped. Thank you so much!

Glad something has worked for you.

However, when there have been multiple suggestions helpers like to know which suggestion(s) were useful so it would be helpful if you identified who you were addressing when making comments like that.

 

[Rick Rothstein]

Here is another macro that should work correctly...

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 "?[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

 

[Livin404]

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

That works great! Thank you, if I wanted to add in the first character would I just simply enter first secondcharacterlist as String after I defined the First Character.

Thank you,

 

[Livin404]

Glad something has worked for you.

However, when there have been multiple suggestions helpers like to know which suggestion(s) were useful so it would be helpful if you identified who you were addressing when making comments like that.

Thank you, I'm sorry I thought I already did. Thanks for helping out!

 

[johnnyL]

That works great! Thank you, if I wanted to add in the first character would I just simply enter first secondcharacterlist as String after I defined the First Character.

Thank you,

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:

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

 

[Livin404]

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:

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

Yes I definetly tried that, I'm not getting any errors, I checked to make sure it was in my Run All list "Call" and it's there. Could it be because these are the first three characters of my text string, and we're using "Mid range". I wouldn't think so, because the the one with just the second and third character works just fine. Thank you.