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

row = 6
NameOld = Cells(4, 1)
Do
NameNew = Cells(row, 1)
Cells(row, 2).Replace What:=NameOld, _
Replacement:=NameNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
row = row + 1
Loop Until Cells(row, 2) = ""

Hth,
Merjet

Terrific! Worked like a charm.
Now for the next wrinkle. I want to do the same change in columns C,D, and
E by running only one macro, ideally one row at a time instead of one column
at a time. The NameNew is still in column A as before. Is it possible to
put in a"column" loop.

"merjet" wrote:

row = 6
NameOld = Cells(4, 1)
Do
NameNew = Cells(row, 1)
Cells(row, 2).Replace What:=NameOld, _
Replacement:=NameNew, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
row = row + 1
Loop Until Cells(row, 2) = ""

Hth,
Merjet

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

disregards,
missed that you had a different target in each row.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote:




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

col = 3
NameOld = Cells(4, 1)
Do
NameNew = Cells(1, col)
Cells(2, col).Replace What:=NameOld, _
Replacement:=NameNew, LookAt:=xlPart, _
SearchOrder:=xlByColumns, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
col = col + 1
Loop Until Cells(2, col) = ""

Hth,
Merjet