# ISSUE WITH LIKE FUNCTION AND STRING



## MadMatLePsyke (Tuesday at 9:26 AM)

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 (Tuesday at 11:17 AM)

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


----------



## Joe4 (Tuesday at 11:36 AM)

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 (Tuesday at 11:52 AM)

Joe4 said:


> 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.
> 
> ...




```
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 (Tuesday at 11:53 AM)

MadMatLePsyke said:


> ```
> Dim LeMail As Variant
> Dim Side As String
> Dim Qty As String
> ...



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


----------



## Joe4 (Tuesday at 1:25 PM)

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 (Wednesday at 1:18 AM)

WsB/WsDL are worksheets
V30 is B or S


----------



## Joe4 (Wednesday at 7:07 AM)

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 (Wednesday at 8:15 AM)

> Public Wb As ThisWorkbook
> Public WsB As Worksheet
> Public WsDL As Worksheet
> 
> ...



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 (Wednesday at 8:23 AM)

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?*


----------



## MadMatLePsyke (Tuesday at 9:26 AM)

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 (Wednesday at 8:25 AM)

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 (Wednesday at 8:42 AM)

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 (Wednesday at 8:59 AM)

RoryA said:


> 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 (Wednesday at 9:02 AM)

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 (Wednesday at 9:12 AM)

RoryA said:


> 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 (Wednesday at 9:14 AM)

MadMatLePsyke said:


> 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 (Wednesday at 9:24 AM)

Alex Blakenburg said:


> 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 "*"
> 
> ...


still no email adress in bcc


----------



## Alex Blakenburg (Wednesday at 9:32 AM)

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 (Wednesday at 9:35 AM)

Alex Blakenburg said:


> 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()
> 
> ...


----------



## MadMatLePsyke (Yesterday at 11:25 AM)

anyone ?


----------



## MadMatLePsyke (Tuesday at 9:26 AM)

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
```


----------



## Alex Blakenburg (Today at 6:08 AM)

Give this a try:
Note: what is in IOI has to be such that the full string is in your email address.
eg MAMAN TRADING is not in test@maman.com, so that won't work
MAMAN is in test@maman.com so that will work.


```
Sub PMBLOCK()

    Dim wb As Workbook
    Dim WsB As Worksheet, WsDL As Worksheet

    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
    Dim cel As Range
    Dim olMailItem As Long
    Dim addrCol As String
    
    With WsB
        Qty = .Range("G27")
        Stock = .Range("C24")
        Name = .Range("E24")
        .Range("E30").Copy
        
        If .Range("V30").Value = "B" Then
            Side = "Block Buyer"
        Else: Side = "Block Seller"
        End If
        
        IOI = "*" & .Range("E30").Value & "*"            ' XXX Needs wild cards
    End With
    
    With WsDL
        .Cells(2, 2).PasteSpecial Paste:=xlValues
        Pays = .Cells(1, 2).Value
    End With
    
    If Pays = "FRANCE" Then
        addrCol = "A"
    Else
        addrCol = "C"
    End If
    
    R = WsDL.Cells(Rows.Count, addrCol).End(xlUp).Row
    With WsDL.Columns(addrCol)
        Set Rng1 = .Range(.Cells(3, 1), .Cells(R, 1))
    End With
    For Each cel In Rng1
        If Not UCase(cel.Value) Like UCase(IOI) Then    ' XXX only include email if "not like"
            Ad = Ad & ";" & cel.Value
        End If
    Next
    Ad = Right(Ad, Len(Ad) - 1)                         ' XXX Trim off leading ";"
    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

End Sub
```


----------



## MadMatLePsyke (Today at 8:06 AM)

Alex Blakenburg said:


> Give this a try:
> Note: what is in IOI has to be such that the full string is in your email address.
> eg MAMAN TRADING is not in test@maman.com, so that won't work
> MAMAN is in test@maman.com so that will work.
> ...


Gonna try

Thx


----------



## MadMatLePsyke (Today at 9:48 AM)

Alex Blakenburg said:


> Give this a try:
> Note: what is in IOI has to be such that the full string is in your email address.
> eg MAMAN TRADING is not in test@maman.com, so that won't work
> MAMAN is in test@maman.com so that will work.
> ...



Unf not working


----------

