Macro to return value if cell contains certain text
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

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

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

    Default Macro to return value if cell contains certain text

    Hello, I would like to write a macro to return values in adjacent cell if a cell contains certain text. For example, if D2 contains any of the following, write the corresponding value in E2; same for D3, D4, etc.:

    If D2 contains "REG_EU", write Europe in E2 and/or
    If D2 contains "CN", write China in E2 and/or
    If D2 contains "US", write North America in E2 and/or
    If D2 contains "REG_WORLD", write North America, Europe and China in E2.

    I've included a sample for reference.

    Any help would be appreciated. Thanks!

    SUBID PFID Short Description Long Description Impacted Region
    200000002401 00004346 Significant Change Specification: 200000002401 with PFID 00004346 Significant change: Complete GHS revisions for specification 200000002401. ||| Substance ID #: 200000002401 CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label. China
    200000002402 00004397 Significant Change Specification: 200000002402 with PFID 00004397 Significant change: Complete GHS revisions for specification 200000002402. ||| Substance ID #: 200000002402 ||| US - Significant change due to HAZARDOUS INGREDIENTS ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label. North America, Europe, China
    200000002411 00004850 Significant Change Specification: 200000002411 with PFID 00004850 Significant change: Complete GHS revisions for specification 200000002411. ||| Substance ID #: 200000002411 ||| REG_EU - Significant change due to HAZARDOUS INGREDIENTS ||| CN - Significant change due to HAZARDOUS INGREDIENTS ||| |||Note significant change reason. Run corresponding " RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label. Europe, China

  2. #2
    Board Regular mumps's Avatar
    Join Date
    Apr 2012
    Location
    Toronto, Canada
    Posts
    8,087
    Post Thanks / Like
    Mentioned
    85 Post(s)
    Tagged
    5 Thread(s)

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

    Try:
    Code:
    Sub dan4()
        Application.ScreenUpdating = False
        Dim LastRow As Long, fnd As Range, region As Range, arr As Variant, i As Long
        LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
        arr = Array(" REG_EU ", " CN ", " US ", " REG_WORLD ")
        For Each region In Range("D2:D" & LastRow)
            For i = LBound(arr) To UBound(arr)
                If InStr(1, region, arr(i)) > 0 Then
                    Select Case arr(i)
                        Case " REG_EU "
                            region.Offset(0, 1).Value = region.Offset(0, 1).Value & ", " & "Europe"
                        Case " CN "
                            region.Offset(0, 1).Value = region.Offset(0, 1).Value & ", " & "China"
                        Case " US "
                            region.Offset(0, 1).Value = region.Offset(0, 1).Value & ", " & "North America"
                        Case " REG_WORLD "
                            region.Offset(0, 1).Value = region.Offset(0, 1).Value & ", " & "North America, Europe,China"
                    End Select
                End If
            Next i
            region.Offset(0, 1).Value = Mid(region.Offset(0, 1).Value, 3)
        Next region
        Application.ScreenUpdating = True
    End Sub
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Board Regular
    Join Date
    Apr 2010
    Posts
    167
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by dan4 View Post
    Hello, I would like to write a macro to return values in adjacent cell if a cell contains certain text. For example, if D2 contains any of the following, write the corresponding value in E2; same for D3, D4, etc.:

    If D2 contains "REG_EU", write Europe in E2 and/or
    If D2 contains "CN", write China in E2 and/or
    If D2 contains "US", write North America in E2 and/or
    If D2 contains "REG_WORLD", write North America, Europe and China in E2.

    I've included a sample for reference.

    Any help would be appreciated. Thanks!

    I think this should get you started.

    Code:
    Sub Go()
        cOutput = Empty
        If InStr(1, Range("D2"), "REG_EU") > 0 Then
            cOutput = cOutput & "Europe, "
        End If
        If InStr(1, Range("D2"), "CN") > 0 Then
            cOutput = cOutput & "China, "
        End If
        If InStr(1, Range("D2"), "US") > 0 Then
            cOutput = cOutput & "North America, "
        End If
        If InStr(1, Range("D2"), "REG_WORLD") > 0 Then
            Range("E2") = "North America, Europe, and China"
        End If
        cOutput = Trim(cOutput)
        If Not IsEmpty(cOutput) Then
            If Mid(cOutput, Len(cOutput), 1) = "," Then
                cOutput = Mid(cOutput, 1, Len(cOutput) - 1)
            End If
            Range("E2") = cOutput
        End If
    End Sub

  4. #4
    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 Steve_ View Post
    I think this should get you started.

    Code:
    Sub Go()
        cOutput = Empty
        If InStr(1, Range("D2"), "REG_EU") > 0 Then
            cOutput = cOutput & "Europe, "
        End If
        If InStr(1, Range("D2"), "CN") > 0 Then
            cOutput = cOutput & "China, "
        End If
        If InStr(1, Range("D2"), "US") > 0 Then
            cOutput = cOutput & "North America, "
        End If
        If InStr(1, Range("D2"), "REG_WORLD") > 0 Then
            Range("E2") = "North America, Europe, and China"
        End If
        cOutput = Trim(cOutput)
        If Not IsEmpty(cOutput) Then
            If Mid(cOutput, Len(cOutput), 1) = "," Then
                cOutput = Mid(cOutput, 1, Len(cOutput) - 1)
            End If
            Range("E2") = cOutput
        End If
    End Sub
    Thank you! This works but only for the first row. I have a range that could contain several rows. Can you please advise how to set a range? Thank you

  5. #5
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    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 MG10Jul30
    Dim Rng As Range, Dn As Range, n As Long, K As Variant
    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
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", .Item(K), ", " & .Item(K))
            End If
        Next K
    Next Dn
    End With
    
    End Sub
    Regards Mick

  6. #6
    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 MG10Jul30
    Dim Rng As Range, Dn As Range, n As Long, K As Variant
    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
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", .Item(K), ", " & .Item(K))
            End If
        Next K
    Next Dn
    End With
    
    End Sub
    Regards Mick
    Hello Mick, thanks this worked! However, I now realize I have blank returns in Column E if the conditions are not met in Column D range. Can you help expand the range and add a condition that if Column C contains "New Specification", write "North America, Europe and China" in E2. Thanks again!

    Here's the logic:
    If D2 contains "Significant change" and "REG_EU", write Europe in E2 and/or
    If D2 contains "Significant change" and "CN", write China in E2 and/or
    If D2 contains "Significant change" and "US", write North America in E2 and/or
    If D2 contains "Significant change" and "REG_WORLD", write North America, Europe and China in E2. and/or
    If C2 contains "New Specification:", write North America, Europe and China.

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

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

    Please clarify !!
    Are you now saying that any row in column "D" must first have "Significant Change" plus one of the other criteria before the code returns an answer in column "E".
    And are you also now saying that ,if there is NO answer returned in column "E" from data in column "D", then look for criteria "New Specification" in column "C", and if found return, "North America, Europe and China" in Column "E", or something else ???

  8. #8
    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
    Please clarify !!
    Are you now saying that any row in column "D" must first have "Significant Change" plus one of the other criteria before the code returns an answer in column "E".
    And are you also now saying that ,if there is NO answer returned in column "E" from data in column "D", then look for criteria "New Specification" in column "C", and if found return, "North America, Europe and China" in Column "E", or something else ???
    Mick, correct. If it is easier to first check column C, then column D, the following inputs would apply: Sorry for the change,.

    Here's the logic:
    If C2 contains "Significantchange" and D2 contains "REG_EU", write Europe in E2 and/or

    If C2 contains "Significant change" and D2contains “CN", write China in E2 and/or
    If C2 contains "Significant change" and D2contains “US", write North America in E2 and/or
    If C2 contains "Significant change" and D2contains “REG_WORLD", write North America, Europe and China in E2 and/or
    If C2 contains "New Specification", write NorthAmerica, Europe and China in E2.

    The Range can contain several hundred rows.


  9. #9
    MrExcel MVP
    Join Date
    Jan 2008
    Posts
    14,820
    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 MG11Jul06
    Dim Rng As Range, Dn As Range, n As Long, K As Variant
    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
         If InStr(1, Dn.Offset(, -1).Value, "Significant Change") > 0 Then
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", .Item(K), ", " & .Item(K))
            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

  10. #10
    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 MG11Jul06
    Dim Rng As Range, Dn As Range, n As Long, K As Variant
    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
         If InStr(1, Dn.Offset(, -1).Value, "Significant Change") > 0 Then
            If InStr(1, Dn.Value, K, vbBinaryCompare) > 0 Then
               Dn.Offset(, 1).Value = Dn.Offset(, 1).Value & IIf(Dn.Offset(, 1).Value = "", .Item(K), ", " & .Item(K))
            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, thanks but the code seems to repeat the output when more than one input value exists in Cell D2. Here's an example:

    Column A Column B Column C Column D Column F - Macro Output
    200000009083 00008358 Significant Change: 200000009083 with PFID: 00008358 Review and either Approve or Reject reports for specification: 200000009083 with PFID: 00008358 Significant change: Complete GHS revisions for specification 200000009083. ||| Substance ID #: 200000009083 ||| 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) ||| |||Note significant change reason. Run corresponding "LINCOLN ELECTRIC RULES" to completion, create reports with languages from generation variants and extract GHS label data, when applicable,for products that require a GHS label. Europe, China, North America, North America, Europe, China

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
  •