Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    search-replace (excel97+)

    I need a macro (vba) which does the following:
    I have a large text column (say col A) which needs to be replaced by text which is in a column (say col E)
    Column D consists of all the possibilities of words which might appear in col A. Col E value for each row is the text which has to replace the text in column A. I know that this is basically a "vlookup" requirement but I dont want to do sorting of columns every time I make an addition and I would like to see how to do it in
    "pure" vba.

    TIA

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    I am having a hard time picturing what your sheet looks like and what you are trying to do. Could you possibly attach a small workbook example of what you want to do?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    replaced column A should read
    ppp
    ggg
    kkkk
    pppp
    P.S-Hope u can read excel file due to language international
    Attached Files Attached Files

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    OK, the following code should do what you are asking. If a value in column A is not found in column D, then the value in column A will be replaced with the value in column E that is one row beyond the last value in column D (an empty cell in your example workbook).

    The following code is in American English.

    <pre>Public Sub MyLookup()
    Dim I As Long, J As Long, lAMax As Long, lDMax As Long
    lAMax = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
    lDMax = Worksheets("Sheet1").Range("D65536").End(xlUp).Row
    For I = 0 To lAMax - 1
    For J = 0 To lDMax - 1
    If Worksheets("Sheet1").Range("A1").Offset(I, 0) = Worksheets("Sheet1").Range("D1").Offset(J, 0) Then
    Exit For
    End If
    Next J
    Worksheets("Sheet1").Range("A1").Offset(I, 0) = Worksheets("Sheet1").Range("D1").Offset(J, 1)
    Next I
    End Sub
    </pre>

    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    GREAT-- I WILL GIVE THEM BOTH A TRY AND LET U KNOW
    THANX FOR YOUR QUICK RESPONSES

  6. #6
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    Thanx---- both solutions worked ike a charm
    Learnt a lot by studying both solutions
    Thanx again
    Smbs

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search-replace (excel97+)

    I went at this a little differently than Legare, but with the same language caveat [Edits in red, as my original code was abysmally slow on large ranges]:

    Sub ReplaceColContent()
    Dim rngSearchForCell As Range, rngTarget As Range
    Dim strSearchFor As String, strReplaceWith As String
    Application.ScreenUpdating = False
    For Each rngSearchForCell In ActiveSheet.UsedRange.Columns("D").Cells
    strSearchFor = rngSearchForCell.Value
    <font color=red> If strSearchFor <> "" Then
    strReplaceWith = rngSearchForCell.Offset(0, 1).Value
    For Each rngTarget In ActiveSheet.UsedRange.Columns("A:A").Cells
    rngTarget.Replace What:=strSearchFor, Replacement:=strReplaceWith
    Next rngTarget
    End If</font color=red>
    Next rngSearchForCell
    Application.ScreenUpdating = True
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: search-replace (excel97+)

    I finally checked my code on a large range, and discovered it was -really- slow, so I have edited it for significant improvement.
    -John ... I float in liquid gardens
    UTC -7ąDS

  9. #9
    2 Star Lounger
    Join Date
    Dec 2001
    Posts
    174
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: search-replace (excel97+)

    Thanx for the update--- I noticed that original was rather slow
    Smbs

Posting Permissions

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