VBA for Groupings of letter and/or numbers to decode

[Livin404]

Greetings,

I'm trying to decode a alpha numeric code. I have several MACROS and they work fine. I know for example

If Data(R, 1) Like "[145678CDJKQXYZF][JPVM][HXZWJURYANM]*"

. That a letter or number within the first braces combined with a letter in the second braces and finally combined with a letter in the third braces will get be the desired text. What I would really like to know: Suppose I have a group of letters/numbers that now rather than a single letter or numbe the macros will look for a pair of letters or numbers. In the example below suppose rather than [HXZWJURYANM] the 3 & 4 characters have to be in pairs preceeded by the braces with single digits, for example (01), (WC), (32) etc.. Again the formula is good below, I just need a little tweak.

Thank you very much indeed.

Sub Valid_Access()
  Dim R As Long, Data As Variant, Result As Variant
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
  Result = Range("F1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If Data(R, 1) Like "[145678CDJKQXYZF][JPVM][HXZWJURYANM]*" Then
      Result(R, 1) = Result(R, 1) & Mid("/Valid Access with Proper ID", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("F1").Resize(UBound(Result)) = Result
End Sub

 

[Zot]

Few lines of examples and expected results would help

 

[Livin404]

Here is an example in Column B I have "FJWC207Z1338". Using the macro provided the first and second characters I covered F in the first braces and J is the second braces. For the third braces I need the MACRO to target the third and fourth characters. How can I include a group of two letters like I did for the single letters?

It would be something like

If Data(R, 1) Like "[145678CDJKQXYZF][JPVM][(WC)(FT)(05)(X5)]*" Then

Thank you,

 

[Livin404]

I'm not working with somelthing like- Hopefully, it will work.

Thank you,

Sub Valid_Access()
  Dim RE As Object
  Dim R As Long, Data As Variant, Result As Variant
  Set RE = CreateObject("VBScript.RegExp")
  RE.Pattern = "^[145678CDJKQXYZF][JPVM](WC|FT|05|X5)"
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
  Result = Range("F1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If RE.Test(Data(R, 1)) Then
      Result(R, 1) = Result(R, 1) & Mid("/Valid Access with Proper ID", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("F1").Resize(UBound(Result)) = Result
End Sub

 

[Zot]

I'm not working with somelthing like- Hopefully, it will work.

Thank you,

Sub Valid_Access()
  Dim RE As Object
  Dim R As Long, Data As Variant, Result As Variant
  Set RE = CreateObject("VBScript.RegExp")
  RE.Pattern = "^[145678CDJKQXYZF][JPVM](WC|FT|05|X5)"
  Data = Range("B1", Cells(Rows.Count, "B").End(xlUp)).Value
  Result = Range("F1").Resize(UBound(Data)).Value
  For R = 1 To UBound(Data)
    If RE.Test(Data(R, 1)) Then
      Result(R, 1) = Result(R, 1) & Mid("/Valid Access with Proper ID", 1 - (Result(R, 1) = ""))
    End If
  Next
  Range("F1").Resize(UBound(Result)) = Result
End Sub

If I understood what you were trying to do, then your code here is working. However, the code will give error if only one row of data for whatever reason

 

[Akuini]

Perhaps something like this:

tx = Data(R, 1)
    If Left(tx, 2) Like "[145678CDJKQXYZF][JPVM]" Then
        If InStr("|WC|FT|05|X5|", Mid(tx, 3, 2)) Then
            Result(R, 1) = Result(R, 1) & Mid("/Valid Access with Proper ID", 1 - (Result(R, 1) = ""))
        End If
    End If