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...

Sub multiFindNReplace()
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
Next cel
End Sub

excedrin1997,

You won't be able to use ranges like this, based on your explanation of what you're trying to do. Basically, you need to just deal with A5 and O5, then when you're done with that, just deal with A6 and O6, and so on. The following is just air code, but it should be basically what you need:



Hope this helps,

Dennis

i am sure others will have other ideas, here is mine.

I tried both codes, and the second one worked perfectly. I see the logic in the first one, but may not have coded it correctly. I appreciate the help, you guys are wonderful!!