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]

IOI is in CAPS, ie : MAMAN TRADING LLC
cel.value = test@maman.com

 

[Joe4]

I think your question is a bit unclear. Your code is pulling from a lot of different cells, and you have not shown us any data, so we have no idea what is in those cells.

Please show us an example, including what is contained in every cell that your code references, and show us what it is returning, and what your expected result is.


Here is a good post on how to psot good questions and some posting tools that may help (i.e. the XL2BB tool): Guidelines

 

[MadMatLePsyke]

I think your question is a bit unclear. Your code is pulling from a lot of different cells, and you have not shown us any data, so we have no idea what is in those cells.

Please show us an example, including what is contained in every cell that your code references, and show us what it is returning, and what your expected result is.


Here is a good post on how to psot good questions and some posting tools that may help (i.e. the XL2BB tool): Guidelines

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")= 10000
Stock = WsB.Range("C24")= ORA FP
Name = WsB.Range("E24") = ORANGE
IOI = WsB.Range("E30").Value = MAMAN TRADING LLC


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

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

    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]

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")= 10000
Stock = WsB.Range("C24")= ORA FP
Name = WsB.Range("E24") = ORANGE
IOI = WsB.Range("E30").Value = MAMAN TRADING LLC


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

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

    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

Rng1 containt email adress, one of these adress is test@maman.com and I want to excluse it from Ad string

 

[Joe4]

Still missing a bunch of information.

What is WsB set to?
What is WsDL set to?
What is the range "V30" on the WsB sheet?

I have a feeling it is going to be very difficult for us to help you without having access to your data sheets.

 

[MadMatLePsyke]

WsB/WsDL are worksheets
V30 is B or S

 

[Joe4]

In order to recreate your scenario and test it out, we need to know the EXACT values of each and every variable at the time you are running this (i.e. what is the name of the sheets that WsB and WsDL are being assigned to? Is V30 "B" or "S" in this test that isn't working for you?)

Remember, while you have all the data in front of you, and you know exactly what you want to happen in what situation, we don't. All we know if what limited amount of information you have decided to share with us here. The more (and complete) information you can share here will increase your odds of getting your question answered.

So it would be very helpful if you could tell us exactly what sheets WsB and WsDL are assigned to, and then show us the sections of your worksheets that show us all the cells being referenced in your code.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[MadMatLePsyke]

Public Wb As ThisWorkbook
Public WsB As Worksheet
Public WsDL As Worksheet

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")

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 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 cel.Value <> "" Then
Ad = Ad & ";" & cel.Value
Else: Ad = Ad
End If
Next
End If

Set LeMail = CreateObject("Outlook.Application")

With LeMail.CreateItem(olMailItem)
.Subject = "*** MEDIOBANCA INTEREST : " & Side & " " & Qty & " " & Stock & " ( " & Name & " ) ***"
.BCC = Ad
.HTMLBody = "We are " & Side & " of " & Qty & " " & Stock & ""
.Display
End With

WsB.Select
Cells(1, 1).Select


End Sub

Here is the full code :
Cell V30= B
Qty = 10,000
Stock = ORA FP
Name = ORANGE
IOI : MAMAN TRADING LLC
Ad = multiple email adress
Rng1 contains email adress / 1 cell of Rng1 is test@maman.com
The main issue is on Like function, I have still the email adress above on bc

 

[Joe4]

OK, I will ask one last time (for the third time):
What is the name of the sheets that WsB and WsDL are being assigned to?