Macro to return value if cell contains certain text
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Macro to return value if cell contains certain text
Thanks Thanks: 0 Likes Likes: 0

  1. #11
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,821
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro to return value if cell contains certain text

    Try this:-
    Code:
    Sub MG12Jul22
    Dim Rng As Range, Dn As Range, n As Long, K As Variant, Sp As Variant, nStr As String
    Set Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        .Item(" REG_EU ") = "Europe"
        .Item(" CN ") = "China"
        .Item(" US ") = "North America"
        .Item(" REG_WORLD ") = "North America, Europe, China"
    
    For Each Dn In Rng
       For Each K In .keys
         nStr = ""
         If InStr(1, Dn.Offset(, -1).Value, "Significant Change") > 0 Then
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               If K = " REG_WORLD " Then
                     Sp = Split(.Item(K), ", ")
                     For n = 0 To UBound(Sp)
                        If InStr(Dn.Offset(, 1).Value, Sp(n)) = 0 Then
                            nStr = nStr & IIf(nStr = "", Sp(n), ", " & Sp(n))
                        End If
                    Next n
               Else
                    nStr = .Item(K)
               End If
               
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", nStr, ", " & nStr)
            End If
         ElseIf InStr(1, Dn.Offset(, -1).Value, "New Specification") > 0 Then
                Dn.Offset(, 1).Value = "North America, Europe and China"
         End If
      Next K
    Next Dn
    End With
    End Sub
    Regards Mick

  2. #12
    New Member
    Join Date
    Nov 2010
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to return value if cell contains certain text

    Quote Originally Posted by MickG View Post
    Try this:-
    Code:
    Sub MG12Jul22
    Dim Rng As Range, Dn As Range, n As Long, K As Variant, Sp As Variant, nStr As String
    Set Rng = Range(Range("D2"), Range("D" & Rows.Count).End(xlUp))
    With CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
        .Item(" REG_EU ") = "Europe"
        .Item(" CN ") = "China"
        .Item(" US ") = "North America"
        .Item(" REG_WORLD ") = "North America, Europe, China"
    
    For Each Dn In Rng
       For Each K In .keys
         nStr = ""
         If InStr(1, Dn.Offset(, -1).Value, "Significant Change") > 0 Then
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               If K = " REG_WORLD " Then
                     Sp = Split(.Item(K), ", ")
                     For n = 0 To UBound(Sp)
                        If InStr(Dn.Offset(, 1).Value, Sp(n)) = 0 Then
                            nStr = nStr & IIf(nStr = "", Sp(n), ", " & Sp(n))
                        End If
                    Next n
               Else
                    nStr = .Item(K)
               End If
               
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", nStr, ", " & nStr)
            End If
         ElseIf InStr(1, Dn.Offset(, -1).Value, "New Specification") > 0 Then
                Dn.Offset(, 1).Value = "North America, Europe and China"
         End If
      Next K
    Next Dn
    End With
    End Sub
    Regards Mick
    Hi Mick, you almost have it. Is it possible to prevent the last comma after China when D2 has repeating text inputs? It works great when D2 has one text input reference. Here is an example. The reason I ask is because I will filter Column E and would like consent returns Thank for your help.

    200000003975 00005923 Significant Change Specification: 200000003975 with PFID 00005923 Significant change: Complete GHS revisions for specification 200000003975. ||| Substance ID #: 200000003975 ||| US - Significant change due to HAZARDOUS INGREDIENTS ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| North America, Europe, China D2 has one of each "US", "REG_EU", CN
    200000009182 00008697 Significant Change Specification: 200000009182 with PFID 00008697 Significant change: Complete GHS revisions for specification 200000009182. ||| Substance ID #: 200000009182 ||| US - Significant change due to HAZARDOUS INGREDIENTS ||| US - Significant change due to GHS LABELING (LIST DATA) ||| US - Significant change due to GHS CLASSIFICATION (NA) ||| US - Significant change due to GHS CLASSIFICATION (LIST DATA) ||| REG_WORLD - Significant change due to LE GHS CLASSIFICATION (NA) ||| REG_WORLD - Significant change due to CHEMICAL CHARACTERIZATION ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| REG_EU - Significant change due to GHS LABELING (LIST DATA) ||| REG_EU - Significant change due to GHS CLASSIFICATION (LIST DATA) ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to GHS LABELING (LIST DATA) ||| CN - Significant change due to GHS CLASSIFICATION (LIST DATA) ||| North America, Europe, China, D2 has multiple "US", "REG_EU", CN. Notice the comma after CN

  3. #13
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,821
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro to return value if cell contains certain text

    Add the 2 lines below shown in red:-
    Code:
     End If
               If Not nStr = "" Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", nStr, ", " & nStr)
               End If
            End If
    Last edited by MickG; Jul 12th, 2019 at 10:37 AM.

  4. #14
    New Member
    Join Date
    Nov 2010
    Posts
    30
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro to return value if cell contains certain text

    Quote Originally Posted by MickG View Post
    Add the 2 lines below shown in red:-
    Code:
     End If
               If Not nStr = "" Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", nStr, ", " & nStr)
               End If
            End If
    It worked! Thank you so much for your help. I really appreciate it1

  5. #15
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,821
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    11 Thread(s)

    Default Re: Macro to return value if cell contains certain text

    You're welcome

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •