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
Bookmarks