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

 

[Scott Huish]

Why can't you just use LEFT?

Sub Test()
For Each c In Range("CA2:CA" & ActiveSheet.UsedRange.Rows.Count)
    If UCase(Left(c, 3)) = "FCN" Then Range("CX" & c.Row) = UCase(Left(c, 11))
Next
End Sub

 

[jvdherik010]

Why can't you just use LEFT?

Sub Test()
For Each c In Range("CA2:CA" & ActiveSheet.UsedRange.Rows.Count)
    If UCase(Left(c, 3)) = "FCN" Then Range("CX" & c.Row) = UCase(Left(c, 11))
Next
End Sub

yes that brilliant. however i have cells that contain more strings in a string.
so 3 or 6 Times a match on (Right)c,3))

so then i need to find last row.. lets say

dim lastcol as integer
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column

but then in a _split methode? with _findstring?
or a _ParramArray?

im not completly sure what do to that each found Instr is transferred to the last collumn on that same row.

maybe you have an idea?

thanksss so muchh,

 

[Scott Huish]

Are there only 10 possibilities that start with FCN like in your example, then do they each go in the appropriate column for each of the 10 possible values?

 

[jvdherik010]

Are there only 10 possibilities that start with FCN like in your example, then do they each go in the appropriate column for each of the 10 possible values?

well, yes FCN they all begin with but i have 1000 variables after that FCN.
therefore i need to search fro FCN + 8 (Left).

but i have cell with aroung 20 of these codes in it.. and all needs to be found. and then that match of FCN +8 needs to be transferred to the last collumn..

or a fix collumn, thats also oke.. but then you need tot have 20 fixed columns see.
there my thought was that last collumn will be better..
and when i have 21 codes in de string, then i don't have a problem with the script

 

[Scott Huish]

Sorry, not quite sure I totally get it. If there are multiple FCN codes in the same cell, you want them all copied to a single cell in the same row? Do you want them delimited by a comma or line breaks or something?

 

[jvdherik010]

Sorry, not quite sure I totally get it. If there are multiple FCN codes in the same cell, you want them all copied to a single cell in the same row? Do you want them delimited by a comma or line breaks or something?

well copied to multiple cell instead of single.
so no delimeter needed.

every code found copy to last empty cell in that row.

so if 5 codes are found in collumn A then it should be copied to B. second code to C. thirth to D etc.

but i have cells with 446+ characters.. where 5 codes are in it of 11 characters long.

 

[Scott Huish]

This is putting the results in Column DA.

Sub Test()
Dim c As Range
For Each c In Range("CA2:CA" & Range("CA" & Rows.Count).End(xlUp).Row)
    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" & Rows.Count).End(xlUp).Offset(1) = UCase(i)
            Next
        End If
    End With
Next
End Sub

 

[jvdherik010]

This is putting the results in Column DA.

Sub Test()
Dim c As Range
For Each c In Range("CA2:CA" & Range("CA" & Rows.Count).End(xlUp).Row)
    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" & Rows.Count).End(xlUp).Offset(1) = UCase(i)
            Next
        End If
    End With
Next
End Sub

yes almost yes. however the result needs to be:
<code>Sheet1.Cells(1, Columns.Count).End(xlToLeft).Column

but it doenst seem working if i replace this...


he just needs to find all patterns of FCN.(8) and then put it every empty collumn.
so
</code>

<code>FCNMAINNLRTM FCNSHANLRTM fdgsgssgggfwe65194 FCNNGBNLRTM</code>	<code>FCNMAINNLRTM</code>	<code>FCNSHANLRTM</code>	FCNNGBNLRTM

<tbody>
</tbody>

<code>and that for every Row xlendup.


now my problem is also that i have to do this in a CSV file. due to 400+ characters in 1 cell is only fully viewed in CSV.
.XLS has just an max of 255 characters per cell.


</code>

 

[Scott Huish]

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