InStr, vbTextCompare, Last.Collumn

[jvdherik010]

hi all,

i am busy with an formula, for a quite big project.

i use this code:

V_End_Of_Table = ActiveSheet.UsedRange.Rows.Count
Dim cell As Range



For Each cell In Range("CA2:CA" & V_End_Of_Table)
If InStr(1, [SUB]cell.Value[/SUB], "fcnswanlrtm", vbTextCompare) > 0 Then
        
        Range("CX" & cell.Row).Value = "FCNSWANLRTM"

    Else
       
    End If
    
Next

the thing is that i have alot of these "codes" like FCNSWANLRTM, and all are different.


i need to find a code that he is searching InStr for "FCN", and then
.value needs to be that "FCN" + 8 characters on the right put in a certain cell in that row.


but i have another challenge..

1 cell can look like:

FCNCANNLRTM

FCNFOCNLRTM

FCNFOSNLRTM

FCNHUANLRTM

FCNLYGNLRTM

fcnmainnlrtmfcnfosnlrtm

FCNNGBNLRTM

FCNNTGNLRTM

<colgroup><col></colgroup><tbody>
</tbody>

and i need every "code"




what i think i need is an _Instr searched for "FCN", and the value is "FCN" + 8 characters on the right _
_{copied to the last.collumn} of the sheet.. but on that same row..


here the link to my file
https://www.dropbox.com/s/t4485ztfux5oj9r/test havens.xlsm?dl=0




i hope someone can help me

 

[jvdherik010]

Sub Test()
Dim c As Range, d As Long
For Each c In Range("CA2:CA" & Range("CA" & Rows.Count).End(xlUp).Row)
    d = 0
    With CreateObject("vbscript.regexp")
        .Pattern = "FCN.{8}"
        .IgnoreCase = True
        .Global = True
        If .Test(c.Text) Then
            Set m = .Execute(c.Text)
            For Each i In m
                Range("DA" & c.Row).Offset(, d) = UCase(i)
                d = d + 1
            Next
        End If
    End With
Next
End Sub

wow, perfect..
thank you so much, that means a breakthrough.

just 1 last final question.

is it possible to have a pattern like.
F{5}NLRTM ?

 

[Scott Huish]

Do you mean F followed by any 5 characters then NLRTM or do you mean 5 Fs followed by NLRTM?

 

[jvdherik010]

Do you mean F followed by any 5 characters then NLRTM or do you mean 5 Fs followed by NLRTM?

Hi Scott

F followed by 5 characters then NLRTM

as that is always the case

thank you so much for your help

 

[jvdherik010]

Hi Scott

F followed by 5 characters then NLRTM

as that is always the case

thank you so much for your help

its an complex code
but i figured out

"[f]{1}[a-zA-Z]{5}[nlrtm]{5}"

is working

thanks so much for your help

 

[Scott Huish]

Or perhaps:

.Pattern = "F.{5}NLRTM"