Find and replace

**lnjsports** · 06-09-2009, 10:59 PM

Hello SHG,

I posted the workbook above in my second post.

**shg** · 06-09-2009, 11:15 PM

I already tested the code I posted against the workbook you originally posted, and it worked fine.

Post one that doesn't work.

**Paul** · 06-09-2009, 11:19 PM

Shg, I think he wants to figure out why his new code doesn't work. He tried to amend it to look at a different description or column.

**lnjsports** · 06-09-2009, 11:31 PM

SHG,

Yes, the code works for all the different column changes but not for the one for [[Description]] to be replace with column D (description)

This is the code I used.

Sub x()
    Dim s       As String
    Dim cell    As Range

    s = "[[" & Range("D1").Text & "]]"

    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
        cell.Replace What:=s, Replacement:=Cells(cell.Row, "D").Text, _
                     LookAt:=xlPart, MatchCase:=True
    Next cell
End Sub

When I run it, I get run time error 13

**shg** · 06-09-2009, 11:35 PM

Thank you, Paul.

Try this:

Sub x()
    Dim sRepl   As String
    Dim sOld    As String
    Dim cell    As Range
    Dim sNew    As String

    sRepl = "[[" & Range("D1").Text & "]]"

    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
        sOld = cell.Value
        sNew = Replace(sOld, sRepl, Cells(cell.Row, "D").Text)
        cell.Value = sNew
    Next cell
End Sub

**lnjsports** · 06-09-2009, 11:43 PM

SHG,

You are a god! That worked perfectly! BUT, there is one issue though. On the second row, it only replaced part of the data that was in the column D.

It looks like that maybe it is limited in how many characters it does. Could that be the case?

**shg** · 06-09-2009, 11:56 PM

Sorry -- try this:

Sub x()
    Dim sFind   As String
    Dim cell    As Range

    sFind = "[[" & Range("D1").Text & "]]"

    For Each cell In Range("C2", Cells(Rows.Count, "C").End(xlUp))
        cell.Value = Replace(cell.Value, sFind, Cells(cell.Row, "D").Value)
    Next cell
End Sub

**lnjsports** · 06-10-2009, 12:18 AM

YOU ARE AWESOME! Works perfectly!

I really do appreciate your time and efforts in helping me.

I did learn a great deal on this as well.

**shg** · 06-10-2009, 08:55 AM

You're welcome. Would you please mark the thread as Solved?

Thread: Find and replace

Thread Tools

Display

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Re: Find and replace

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions