I know coming to this site that one of you guys will have the answer! I've got a macro that looks up several fields in a column, and replaces a word in the formula of that field with the value of another cell. What i'm trying to do is look at cell "O5" find a word and replace it with the value from cell "A5". I'm trying to do the same for "O6" with value from "A6" and so on down to "O28" being replaced with "A28", and its the same word in every "O" column cell. The problem i'm having with my macro is that it takes the value from "A5" and uses it to replace every occurence of the word in every cell called in column O instead of going to the next cell ("A6") and using that to replace the word in "O6". My code is below...any help would be appreciated...
Dim myList, myRange
Set myList = Sheets("sheet1").Range("A5:A28")
Set myRange = Sheets("sheet1").Range("O5:O28")
For Each cel In myList
myRange.Replace What:="mywordtoreplace", Replacement:=myList.Value