- Sep 20, 2012
Hello, I am writing a macro that will find several different values in sheet1 and replace them with values from a table in sheet2. It currently does exactly that but I realized that there are instances when replacing hyphens isn't appropriate and will ruin the upload of my data. I need to replace it in the rest of the columns though. The headers will always be the same, such as "MST" , so I could use that as a conditional statement I just don't know how to go about it with the replace function. Any suggestions?
Sub FindAndReplace() Dim LastRow As Integer Dim i As Integer 'Counter to loop through all the rows found in the table 'Finds the last row used in my replacement table and sets that as the searchable range LastRow = Sheets("Sheet2").Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row Sheets("Sheet1").Select For i = 1 To LastRow 'Replace the value in column 1 of replacement table with value in column 2 Worksheets("Sheet1").Cells.Replace _ What:=Worksheets("Sheet2").Cells(i, 1).Value, Replacement:=Worksheets("Sheet2").Cells(i, 2).Value, _ SearchOrder:=xlByColumns, MatchCase:=True Next i End Sub