Sub NameReplace()
Dim NameOld
Dim NameNew
NameOld = Cells(4, 1)
NameNew = Cells(row, 1)

Range("B6:B600").Replace What:=NameOld, _
Replacement:=NameNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False

End Sub

--
Regards,
Tom Ogilvy


"msdrolf" wrote:

In column B I have a linking formula to a specific file. In column A I have
about 200 rows with different file names in each row. I would like to replace
the file name in the linking formula in column B with the file name in Column
A so that then the resulting linking formula in column B would link to the
file name listed in column A. I have the following macro but it only
replaces the first row. I would appreciate help to change the macro so it
continues down column B until it reached a blank cell.

Dim NameOld
Dim NameNew
Dim row

Sub NameReplace()

Range("B6").Select
For row = 6 To 600
NameOld = Cells(4, 1)
NameNew = Cells(row, 1)


Selection.Replace What:=NameOld, Replacement:=NameNew, LookAt:=xlPart _
, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

If Cells(row + 1, 1) = "" Then Exit Sub
Next row
End Sub