Excluding certain columns with find and replace

[BrandynBlaze]

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

 

[patel45]

instead of Cells in

Worksheets("Sheet1").Cells.Replace

you can use a range

Worksheets("Sheet1").range("A1:B50").Replace

 

[BrandynBlaze]

Okay, that will get me closer, but I can't guarantee that the headers that shouldn't be modified are going to be in the same place each time. Maybe I could parse the whole used range by column and use a conditional statement to exclude certain columns based on their header? Is there a way to add a range to the already selected range? For instance if I had a loop that had already selected the range("A:A") and then checked the header in Range("B1"), found that it should be selected, could I add range("B:B") without referencing the already selected sections?