If cell contains letters R and P

[emukiss10]

Hello,

I like to copy row based on multiple conditions and I hacve a problem with letters. I like to check if cell in column O contains letters P and R, and if so, copy row to next sheet.

my code is (with not working P and R)

Dim cell1 As RangeDim lastRow1 As Long, i As Long


lastRow1 = Range("N" & Rows.Count).End(xlUp).Row
i = 2


For Each cell1 In Sheets(1).Range("N1:N" & lastRow1)
    If cell1.Value = "TRANSFERING" [B]And Cells(cell1.Row, 15) [/B]=[B] "*P*" And Cells(cell1.Row, 15) [/B]= [B]"*R*" [/B]Then
        cell1.EntireRow.Copy Sheets("fullList").Cells(i, 1)
        i = i + 1
    End If
Next

Please help

Best Regards
W.

 

[wideboydixon]

    If cell1.Value = "TRANSFERING" And Cells(cell1.Row, 15) Like "*P*" And Cells(cell1.Row, 15) Like "*R*" Then

WBD

 

[emukiss10]

Works Great!

Can You modify it to accept not any P and R but P and R with numbers like :

P11111111111 (value nor lenght does not matter)
R1111115555555

or

P 0000000000
R 1111111111111

(there can be space between P/R and numbers)

I dont wont to catch P 11111111 with name maRk - beacuse of R in name.

 

[wideboydixon]

If it has to start with P or R then just remove the first asterisk and group the conditions:

    If cell1.Value = "TRANSFERING" And (Cells(cell1.Row, 15) Like "P*" Or Cells(cell1.Row, 15) Like "R*") Then

WBD

 

[emukiss10]

It doesnt... the cell may look like this


P 000000000 R 000000000
or
P111222211R11122222
or
P,121212121,R222111121

and it is correct but I dont want

P111222111 MaR2017

 

[steve the fish]

If you are using Sheets(1) then you should use it when you use Cells, ie Sheets(1).Cells.

 

[wideboydixon]

Then you're in regular expression territory and your original code becomes something more like this:

Dim cell1 As Range
Dim lastRow1 As Long, i As Long
Dim regex As Object

lastRow1 = Range("N" & Rows.Count).End(xlUp).Row
i = 2

Set regex = CreateObject("VBScript.RegExp")
regex.Global = True
regex.Pattern = "P[\s,]?[0-9]+[\s,]?R[\s,]?[0-9]+"

For Each cell1 In Sheets(1).Range("N1:N" & lastRow1)
    If cell1.Value = "TRANSFERING" And regex.Test(Cells(cell1.Row, 15).Value) Then
        cell1.EntireRow.Copy Sheets("fullList").Cells(i, 1)
        i = i + 1
    End If
Next

WBD

 

[emukiss10]

Thank You WBD,

unfortunetly it still catches letters in names like AUREIL (the R)

After letters P and R there will allways be a special char like " ", ",", ".", "/", "-" etc or number without spaces (or two spaces lik "P 121212121" but never other letters

 

[wideboydixon]

The regular expression above most certainly does not match on AUREIL. What is the exact contents of the cell that is matching?

WBD

 

[emukiss10]

Im sorry Sir, I was sure I've checked it. You are right. It does not take the name as I have suggested.