Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In the enclosed spreadsheet I want a macro to go through the list in columns A to D, search for each item in cells J8 to J13 and replace found items with the corresponding item in cells K8 to K13.

    How can I do this easily?

    Thanks for your help in advance!
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Something like this perhaps (test on a copy!)

    Steve

    Code:
    Option Explicit
    Sub FindReplaceList()
      Dim rList As Range
      Dim rCell As Range
      Dim sTemp As String
      Dim AWF As WorksheetFunction
      Set AWF = Application.WorksheetFunction
      Set rList = Range("J8:K13")
      For Each rCell In Range("A1").CurrentRegion
    	sTemp = ""
    	On Error Resume Next
    	sTemp = AWF.VLookup(rCell, rList, 2, 0)
    	On Error GoTo 0
    	If sTemp <> "" Then
      	rCell = sTemp
    	End If
      Next
      Set rCell = Nothing
      Set rList = Nothing
      Set AWF = Nothing
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Here is another option:

    Code:
    Sub ReplaceIt()
    Dim myRange As Range
    Dim myList()
    Dim sfind
    
    Set myRange = Range("Table1")
    myList = Range("WHSRge")
    
    For sfind = 1 To UBound(myList)
        myRange.Replace what:=myList(sfind, 1), replacement:=myList(sfind, 2), lookat:=xlWhole
    Next
    End Sub

  4. #4
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Brilliant, thanks very much, greatly appreciated.

Posting Permissions

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