Consulting

Results 1 to 4 of 4

Thread: crieria item in list to replace in another column

  1. #1
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location

    crieria item in list to replace in another column

    1) List of items to search in column A
    2) Range where list is. Contains text to replace


    3) Code must loop Column A
    4) Search Range("B:B")
    5) Replace whatever search item from A is with the word "rate"

    Sub Test()
        Dim Sh As Worksheet
          
        Set Sh = Sheets("Sheet1")
        For Each Cel In Sh.Columns(1).SpecialCells(2)
            With Sh.Cells
                Set c = Sh.Columns(2).Find(Cel, lookat:=xlPart)
    
    
                Do Until Cel Is Nothing
                    If Not Cel Is Nothing Then ' if found an item
      
                     Range("B:B").Replace What:=Cel, Replacement:="rate", lookat:=xlPart
                    End If
    Cel = Cel.Offset(1, 0).Value
                Loop
                
            End With
        Next
    End Sub

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    6,890
    Location
    With Sh
       For Each Cel In .Columns(1).SpecialCells(2)
          Set c = .Columns(2).Find(Cel, lookat:=xlPart)
          Do Until c Is Nothing
             c.Replace What:=Cel, Replacement:="rate", lookat:=xlPart
             Set c = .Findnext c
          Loop
       Next
    End With
    Please take the time to read the Forum FAQ

  3. #3
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location
    .

  4. #4
    VBAX Regular
    Joined
    Jul 2019
    Posts
    36
    Location
    Thanks SamT,works a charm!

Tags for this Thread

Posting Permissions

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