Macro to amend part of formula repeatedly for remainder of sheet

Hi,

I am trying to write a Macro to replace the value: 100-570-015 within a formula, with the number in column A of that line, and repeat for every line in the list. The incorrect macro i have is as follows:

Do Until IsEmpty(ActiveCell)

ActiveCell.EntireRow.Select
ActiveCell.Copy
Selection.Formula.Replace What:="100-570-015", Replacement:=ActiveCell.Value, LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Selection.Offset(1, 0).Select
Loop

End Sub

I need the value in column A to replace the original value in every cell in each row.

Help would be much appreciated.

Thanks.

Hi
The easiest way to do this is to store your value in a separate cell (this can be on a separate worksheet if required) and refer to that cell within your formula. That way you can change the value in the relevant cell and all your formulae will use the new value without having to use VBA.
Hope this helps.
Tony

Originally Posted by ARGK

Hi
The easiest way to do this is to store your value in a separate cell (this can be on a separate worksheet if required) and refer to that cell within your formula. That way you can change the value in the relevant cell and all your formulae will use the new value without having to use VBA.
Hope this helps.
Tony

Hi Tony,

The value changes with each line. Currently the rows all have same formula as created the references to external sheet in the correct cells, however now want to amend the rows below to refere to their specific sheet dependant on the value in A2, adding these values on another sheet would mean I still have to rewrite formulas doesn't it? Which for 920 lines and 54 columns is a lot of rewriting.

example of the cell formula is:
='W:\File\File\File\File\Stock Build\[Tracker.xlsx]100-570-015'!$E$16