Results 1 to 1 of 1

Macro to find header information and replace with specific text

Threaded View

  1. #1
    Registered User
    Join Date
    02-14-2013
    Location
    Indianapolis, IN
    MS-Off Ver
    Excel 2003
    Posts
    1

    Macro to find header information and replace with specific text

    I am trying to write a macro for excel to find header information with several possible header options and replace with specific text I choose. My current code will find and replace the header but the result is not what i specified. For instance if one of the choices to find and replace is "Site number" and I want to replace it with "Site*" the result is "Site* number". I know the error is due to multiple selection in the loop, but I do not know how to fix it.

    Here is my current code:
    Sub mcrReplaceSiteNumber()
    ' macro to replace the site number header with the correct header
    ' mcrReplaceSiteNumber Macro
    ' Macro recorded 2/14/2013 by ntaylor
     Dim strFind As String, strReplace As String
        Dim ILoop As Long
        ''Declaring each item and setting the definition below eliminates the need to repeat the command for each cell.
        On Error Resume Next
        Sheets("Original").Select
        'Sets the sheet to work in as "Original"
        Range("A1:AK1").Select
        'Selects the cells to work in as the header cells.  This is dependent on the user deleting all rows above the header.
        For ILoop = 1 To 14
        strFind = Choose(ILoop, "Find", "Values", 1, "to", 14, "Site number*", "Site", "Site Reference", "Site Number", _
            "ID", "Site #", "Inv #", "GNE site number", "Unique Id", "Investigator Site Number", "Inv Number", "Investigator Number", _
            "Site ID", "Site ID #")
            'Checks the header and tells the code what to find.
            strReplace = Choose(ILoop, "Replace", "Values", 1, "to", 14, "Site*")
            'Checks the list for the placeholder equivalent to the found item and replaces it with the text list.
            Selection.Replace What:=strFind, Replacement:=strReplace, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=True, SearchFormat:=False, _
            ReplaceFormat:=True
            'This is the command line that tells the the code to run and check the headers for items listed in strfind and replace
            'them with the associated items listed in strReplace
        Next ILoop
            'Tells the code to loop through again and look for the next header name on the list.
    End Sub
    Last edited by Nancy Taylor; 02-15-2013 at 04:52 PM.

Thread Information

Users Browsing this Thread

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

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