Excel VBA iterate over all used cells and substitute their values

Question

This is my first time ever working with VBA and Excel and my problem is probably caused by a big lack of knowledge on my part.

I have a one column list of city names (containing 800 items, hence I'm looking to automatically replace them) and some chunks of text in which the term "cityname" occurs multiple times and needs to be replaced by the correct city name, which in my setup would be the value of the first column cell in the same row.

So I found this: How to iterate through all the cells in Excel VBA or VSTO 2005

and found the InStr and Substitute functions by looking through the Excel VBA reference.

Using the iteration like this works fine:

Sub MySubstitute()
    Dim cell As Range
        For Each cell In ActiveSheet.UsedRange.Cells
            If InStr(cell.Value, "cityname") > 0 Then
                MsgBox "Hello World!"
        End If
    Next cell
End Sub

I get a message box for every "cityname" in my sheet (a test sheet with only 2 rows).

However when I add what I really want to achieve I get a Runtime Error (1004):

Sub MySubstitute()
    Dim cell As Range
    For Each cell In ActiveSheet.UsedRange.Cells
        If InStr(cell.Value, "cityname") > 0 Then
            cell.Value = WorksheetFunction.Substitute(cell.Value, "cityname", Cells(cell.Row, A))
        End If
    Next cell
End Sub

So I guess something in that line is wrong but I can't figure out what. Any hints to what my mistake is are appreciated, thanks :)

score 3 · Answer 1 · answered Jan 28 '16 at 16:17

3

You can use the Range.Replace Method and replace all at once no need to iterate.

Sub MySubstitute()
    Dim rng As Range
    Set rng = ActiveSheet.UsedRange.Cells
    rng.Replace "cityname", "correctcityname", xlPart
End Sub

answered Jan 28 '16 at 16:17

Scott Craner

148,073
10
49
81

This would work if I had a single correct city name. But I have a list of 800, so each time the correct one has to be taken from the first cell of the same row as the current cell, hence the need for iteration. Thanks for the effort though! – nalrya Jan 28 '16 at 16:27

score 1 · Accepted Answer · answered Jan 28 '16 at 16:17

1

You should change:

 cell.Value = WorksheetFunction.Substitute(cell.Value, "cityname", Cells(cell.Row, A))

by

 cell.Value = WorksheetFunction.Substitute(cell.Value, "cityname", Cells(cell.Row, 1))

answered Jan 28 '16 at 16:17

manu

942
8
17

1

it could also be `Cells(cell.Row, "A")` – Scott Craner Jan 28 '16 at 16:19

Excel VBA iterate over all used cells and substitute their values

2 Answers2

Linked