ISSUE WITH LIKE FUNCTION AND STRING

[MadMatLePsyke]

Below my code
IOI ="xxx yyy zzz "
My issue with my IOI string is an email adress, I have one email on my Rng1 but Vba not going to Else

Could you please help me ?

Sub PMBLOCK()

Set Wb = ThisWorkbook
Set WsB = Sheets("BOARD")
Set WsDL = Sheets("DL")

Dim LeMail As Variant
Dim Side As String
Dim Qty As String
Dim Stock As String
Dim Name As String
Dim IOI As String
Dim Pays As String
Dim R As Integer
Dim Rng1 As Range
Dim Ad As String

WsB.Select

Qty = WsB.Range("G27")
Stock = WsB.Range("C24")
Name = WsB.Range("E24")
IOI = WsB.Range("E30").Value


    If WsB.Range("V30").Value = "B" Then
            Side = "Block Buyer"
            Else: Side = "Block Seller"
    End If

  
WsDL.Select
Pays = Cells(1, 2).Value

    If Pays = "FRANCE" Then
        R = Application.WorksheetFunction.CountA(Range("A:A"))
        Set Rng1 = Range(Cells(3, 1), Cells(R, 1))
            For Each cel In Rng1
                If Not cel.Value Like IOI And cel.Value <> "" Then
                    Ad = Ad & ";" & cel.Value
                Else: Ad = Ad
                End If
            Next
        Else: R = Application.WorksheetFunction.CountA(Range("C:C"))
        Set Rng1 = Range(Cells(3, 3), Cells(R, 3))
            For Each cel In Rng1
                If Not cel.Value Like IOI And cel.Value <> "" Then
                    Ad = Ad & ";" & cel.Value
                Else: Ad = Ad
                End If
            Next
    End If

 

[MadMatLePsyke]

Set Wb = ThisWorkbook
Set WsB = Sheets("BOARD")
Set WsDL = Sheets("DL")
On the code so :
The name of WsB is BOARD
The name of WsDL is DL

 

[RoryA]

Why would it go to the Else part? If the cell value is not like IOI (and it isn't in your example) then the first part of the If clause is true. The cell value is also not "", so that part is true too, and the whole If condition is true.

 

[MadMatLePsyke]

Why would it go to the Else part? If the cell value is not like IOI (and it isn't in your example) then the first part of the If clause is true. The cell value is also not "", so that part is true too, and the whole If condition is true.

My issue is when IOI is ie MAMAN TRADING and in Rng1 I have an email adress ie test@maman.com it is not going to the Else part, but adding this email adress to my Ad string
Don't understant why

 

[RoryA]

Because those two value are not the same. Therefore cell.value Like IOI is False. Not False =True, so both parts of the If clause are true.

 

[MadMatLePsyke]

Because those two value are not the same. Therefore cell.value Like IOI is False. Not False =True, so both parts of the If clause are true.

Agreed
But if I'm doing

If cel.value like IOI and cel.value <>""

I don't have any email adress in BCC / ad string value is ""

 

[Alex Blakenburg]

My issue is when IOI is ie MAMAN TRADING and in Rng1 I have an email adress ie test@maman.com it is not going to the Else part, but adding this email adress to my Ad string
Don't understant why

1) the like statement is case sensitive
2) What is on the right has to "part" of what is on the left
3) You need wildcards "*"

Something like this would work.

ucase("test@maman.com") like "*MAMAN*"

 

[MadMatLePsyke]

1) the like statement is case sensitive
2) What is on the right has to "part" of what is on the left
3) You need wildcards "*"

Something like this would work.

ucase("test@maman.com") like "*MAMAN*"

still no email adress in bcc

 

[Alex Blakenburg]

I have logged off for the night but I don't believe your code is showing us where you are assigning something to "to or cc or bc" and both your If and else parts are using the variable Ad, so we really can't understand how you expect to give a different value to bc.

 

[MadMatLePsyke]

I have logged off for the night but I don't believe your code is showing us where you are assigning something to "to or cc or bc" and both your If and else parts are using the variable Ad, so we really can't understand how you expect to give a different value to bc.

my code is :

Sub PMBLOCK()

Set Wb = ThisWorkbook
Set WsB = Sheets("BOARD")
Set WsDL = Sheets("DL")

Dim LeMail As Variant
Dim Side As String
Dim Qty As String
Dim Stock As String
Dim Name As String
Dim IOI As Variant
Dim Pays As String
Dim R As Integer
Dim Rng1 As Range
Dim Ad As String

WsB.Select

Qty = WsB.Range("G27")
Stock = WsB.Range("C24")
Name = WsB.Range("E24")
Range("E30").Copy

If WsB.Range("V30").Value = "B" Then
Side = "Block Buyer"
Else: Side = "Block Seller"
End If

IOI = Range("E30").Value


WsDL.Select
Cells(2, 2).PasteSpecial Paste:=xlValues



Pays = Cells(1, 2).Value

If Pays = "FRANCE" Then
R = Application.WorksheetFunction.CountA(Range("A:A"))
Set Rng1 = Range(Cells(3, 1), Cells(R, 1))
For Each cel In Rng1
If cel.Value Like IOI Then
Ad = Ad & ";" & cel.Value
Else: Ad = Ad
End If
Next
Else: R = Application.WorksheetFunction.CountA(Range("C:C"))
Set Rng1 = Range(Cells(3, 3), Cells(R, 3))
For Each cel In Rng1
If cel.Value Like IOI Then
Ad = Ad & ";" & cel.Value
Else: Ad = Ad
End If
Next
End If

Set LeMail = CreateObject("Outlook.Application")

With LeMail.CreateItem(olMailItem)
.Subject = "*** XXXXX : " & Side & " " & Qty & " " & Stock & " ( " & Name & " ) ***"
.BCC = Ad
.HTMLBody = "XXXX ""
.Display
End With

WsB.Select
Cells(1, 1).Select

 

[MadMatLePsyke]

anyone ?