VBA For Each Loop only excutes once and then stops

Question

I made a VBA macro that has the goal of doing several find and replaces on a database using a set of items in a table. I used a for each loop to try to iterate through each cell in the first column, but it only executes a find and replace on the items in the first column.

The table has two columns, "Replace What" and "Replace With" so i used find and replace to look for the item in the first column and used the offset function to replace it with the corresponding item in the next column.

How can I make this loop work?

Thanks

Here's my code:

Sub FindReplace()
Dim RepList As Range, RepItem As Range



    Set RepList = Worksheets("Supply Replacement").Range("Table4[Replace What]")

    For Each RepItem In RepList.Cells

        Cells.replace What:=RepList.Value, Replacement:=RepList.Offset(0, 1).Value

    Next RepItem

End Sub

Isn't RepItem supposed to be a "Variant"? – EStraka Jul 31 '14 at 15:57 — EStraka, Jul 31 '14 at 15:57

score 1 · Answer 1 · answered Jul 31 '14 at 15:59

1

Within your loop you should be working with RepItem, not RepList:

Sub FindReplace()
Dim RepList As Range, RepItem As Range



    Set RepList = Worksheets("Supply Replacement").Range("Table4[Replace What]")

    For Each RepItem In RepList.Cells

        Cells.replace What:=RepItem.Value, Replacement:=RepItem.Offset(0, 1).Value

    Next RepItem
End Sub

answered Jul 31 '14 at 15:59

Tim Williams

154,628
8
97
125

Wow what a dopey mistake. Thanks, Tim – user3784662 Jul 31 '14 at 16:08

VBA For Each Loop only excutes once and then stops

1 Answers1

Linked