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,
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

johnnyL

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

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

Well-known Member
Joined
Nov 7, 2011
Messages
2,091
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
I think it will take more than that, but please let us know how you do and if you need further assistance
 

Livin404

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

ADVERTISEMENT

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

johnnyL

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

Which error did you get in your endeavor?
 

Livin404

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

ADVERTISEMENT

here is an image. Thank you,
Which error did you get in your endeavor?
error.JPG
 

johnnyL

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,631
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
Can you explain in clear words exactly what the code you posted in Message #1 is supposed to be parsing?
 

johnnyL

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

VBA Code:
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.
 
Solution
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,697
Messages
5,765,982
Members
425,320
Latest member
Galin

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