+ Reply to Thread
Results 1 to 3 of 3

Using a macro to search for text within a cell and then add more text to that cell

  1. #1
    Registered User
    Join Date
    05-12-2006
    Posts
    10

    Using a macro to search for text within a cell and then add more text to that cell

    I have two worksheets, the first contains a column of text values, the second contains three columns of text values. What I need to do is look at the first sheet and where it contains data from the first two columns of the second sheet, append the third column to the text. eg

    1st sheet:
    aaabbbccc
    aaacccddd
    aaabbbddd
    bbbcccddd

    2nd sheet
    aaa bbb eee
    bbb ccc fff

    afterwards I want the 1st sheet to look like:
    aaabbbccceee
    aaacccddd
    aaabbbdddeee
    bbbcccdddfff

  2. #2
    Forum Contributor
    Join Date
    11-11-2005
    Posts
    267
    This works:

    Sub SearchAndAppend()
    Dim Wksht1 as Worksheet, Wksht2 as Worksheet
    Dim c as range

    Set Wksht1=Sheets("Sheet1")
    Set Wksht2=Sheets("Sheet2")

    For each c in Wksht1.Range("a1:a" & Cells(rows.count,"a").End(xlUp).row)
    If c.value = WkSht2.Cells(c.row, "a") & WkSht2.Cells(c.row, "b") then
    c.value = c.value & WkSht2.Cells(c.row, "c").value
    end if
    Next

    End if
    HTH
    Myles

    ...constantly looking for the smoother pebble while the whole ocean of truth lies before me.

  3. #3
    Registered User
    Join Date
    05-12-2006
    Posts
    10
    I couldn't get the above script to work correclty, I have made changes to it so that it now looks through every row, rather than just one, and have also tried adding wild cards, but this doesn't seem to have helped. My script looks like this:

    Sub SearchAndAppend()
    Dim Wksht1 As Worksheet, Wksht2 As Worksheet
    Dim c As Range

    Set Wksht1 = Sheets("Sheet2")
    Set Wksht2 = Sheets("Sheet3")

    For Each c In Wksht1.Range("BI5:BI" & Cells(Rows.Count, "BI").End(xlUp).Row)
    For Each d In Wksht2.Range("C1:C" & Cells(Rows.Count, "C").End(xlUp).Row)
    If c.Value = Wksht2.Cells(d.Row, "a") & "*" & Wksht2.Cells(d.Row, "b") & "*" Then
    c.Value = c.Value & Wksht2.Cells(c.Row, "c").Value
    End If
    Next
    c.Value = c.Value & Wksht2.Cells(1, "d").Value
    Next

    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1