+ Reply to Thread
Results 1 to 3 of 3

Macro to compare text in ranges to find match and replace with adjacent value.

  1. #1
    Registered User
    Join Date
    04-26-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003
    Posts
    31

    Macro to compare text in ranges to find match and replace with adjacent value.

    Hi,

    Having dipped in and out of this forum for the past 506 days (I am reminded) I finally get round to my first post. I am an Informatics Nurse working on a rostering project. Due to our recession in Ireland my service does not have the resources to hire programming expertise so I have undertaken to do the work. I am using Excel 2003 to manage the rostering process and have made good progress to date, having a working system up and running. I am now trying to improve the process by automating certain elements. I need a macro to assist me manage changes of duty (i.e. where one staff member works for another on a shift). I am attaching a worksheet to help outline the task. The range A7:D28 contains a main list of all staff normally available for the shift, range E31:E45 contains the names of staff from this main list who have staff from an opposite shift in D31:D45, working for them. My objective is to search in the main list for matches of staff who have others working for them and replace the name in the relevant cell. e.g Staff 57 at D31 matches C22, - replace with staff at C31. Hope this explains what I need.

    Many thanks in Advance.

    Changes.xls

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Macro to compare text in ranges to find match and replace with adjacent value.

    Sub Macro1()

    Dim myarray As Variant

    On Error Resume Next

    myarray = Range("C31:D45")

    Range("A7:D28").Select

    Count = 0

    10 Count = Count + 1: If Count = 16 Then GoTo 200
    If myarray(Count, 1) = "" Or myarray(Count, 2) = "" Then GoTo 200

    Selection.Replace What:=myarray(Count, 2), Replacement:=myarray(Count, 1), LookAt:= _
    xlWhole, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
    GoTo 10

    200 Range("A7").Select
    End Sub
    Last edited by mehmetcik; 09-11-2013 at 06:12 PM.

  3. #3
    Registered User
    Join Date
    04-26-2012
    Location
    Dublin, Ireland
    MS-Off Ver
    Excel 2003
    Posts
    31

    Re: Macro to compare text in ranges to find match and replace with adjacent value.

    Thanks mehmetcik, this works perfectly. Now I wish I could understand how it works - more study to do ! Thanks again

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Macro to find all specific values in column and replace adjacent cell values
    By dblock02 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-23-2013, 06:03 AM
  2. [SOLVED] Help writing a macro to find text and copy values to adjacent cells
    By Nnnnnnnick in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-24-2012, 10:07 AM
  3. Find and Match Text in Column Return Adjacent Cell
    By biancam in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-11-2012, 02:45 PM
  4. find in excel replace in word: find/replace text in text boxes and headers
    By dean.rogers in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-10-2012, 12:40 PM
  5. Find, Replace with and then replace adjacent cell
    By Craig2097 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-18-2009, 12:42 PM

Tags for this Thread

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