ISSUE WITH LIKE FUNCTION AND STRING

M

MadMatLePsyke

New Member
Joined
Jan 3, 2023
Messages
18
Office Version
  1. 365
Platform
  1. Windows
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 ?

VBA Code: 
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
 
Sort by date Sort by votes
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.

VBA Code: 
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
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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.

VBA Code: 
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
Click to expand...
Gonna try

Thx
 
Upvote 0
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.

VBA Code: 
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
Click to expand...

Unf not working
 
Upvote 0
Do you know how to use the immediate window in VBE ?
If you don't see it when you are in VBA hit Ctrl+G to open it.

Then run the code below which include lots of debug.print lines.
Have a look at what is there and see if there is anything that is obvious that is incorrect.
If you can't see anything go to the the immediate window then Ctrl+A, Ctrl+C and paste it all into a post for us to have a look at.

VBA Code: 
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
    
    ' ---- XXX ----
    Debug.Print "Column: " & addrCol
    Debug.Print "Last Row: " & R
    Debug.Print Rng1.Address(, , , 1)
    Debug.Print "IOI (WsB E30): " & IOI
    ' -------------
    
    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
        ' ---- XXX ----
        Debug.Print "cel value: " & cel.Value
        Debug.Print "Ad value: " & Ad
        ' -------------
        
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Change Log With VBA
2
Replies
19
Views
374
Joe4
Joe4
M
  • Question
Run time error 91
Replies
2
Views
361
MARK858
MARK858
S
  • Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
334
sspatriots
S
E
  • Question
This code is to put a excel range onto a email
Replies
3
Views
411
I_know_nuffin
I
I
  • Question
Userform does not show leading 0 for telephone number
Replies
1
Views
54
ipbr21054
I

Forum statistics

Threads
1,214,987
Messages
6,122,618
Members
449,092
Latest member
amyap

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top