Excel VBA Looping through cells and replacing their values

Question

I am trying to build a macro that cycles through a column of cells and replaces a two letter country code in that cell with the name of that country. However I get an object not found error when I try to run the macro.

Sub ChangeCountryText()
'
' ChangeCountryText Macro
' Changes country codes
'
    For counter = 2 To 20
        Set curCell = ActiveSheet.Cells(counter, 1)
        Select Case curCell.Text
            Case "JP"
                curCell.Text = "Japan"
            Case "FR"
                curCell.Text = "France"
            Case "IT"
                curCell.Text = "Italy"
            Case "US"
                curCell.Text = "United States"
            Case "NL"
                curCell.Text = "Netherlands"
            Case "CH"
                curCell.Text = "Switzerland"
            Case "CA"
                curCell.Text = "Canada"
            Case "CN"
                curCell.Text = "China"
            Case "IN"
                curCell.Text = "India"
            Case "SG"
                curCell.Text = "Singapore"
        End Select
    Next counter

End Sub

What line do you get object not found? When I tested, curCell.Text caused a problem, which can be corrected by using curCell.Value. This code is also case sensitive. — Fionnuala, Nov 09 '10 at 23:12
Yes, implementing that change solves the issue. Thanks for the advice. — Andrew Lauer Barinov, Nov 11 '10 at 18:55

score 6 · Accepted Answer · edited Nov 10 '10 at 02:58

6

The Text property is read-only - you can't set it. Assign to the Value property and it should work (e.g. curCell.Value = "Japan")

edited Nov 10 '10 at 02:58

Joel Spolsky

33,372
17
89
105

answered Nov 10 '10 at 02:20

Dick Kusleika

32,673
4
52
73

score 2 · Answer 2 · answered Nov 10 '10 at 03:00

2

I'm sure you have a great reason for using a macro for this, but you may want to look into the LOOKUP or VLOOKUP worksheet functions as a way to do something like this without writing a macro.

answered Nov 10 '10 at 03:00

Joel Spolsky

33,372
17
89
105

Thanks for the suggestion, I intend to rerun this procedure on multiple workbooks/worksheets so I wanted to get around copying and pasting the cells containing VLOOKUP information. – Andrew Lauer Barinov Nov 11 '10 at 18:53

score 0 · Answer 3 · answered Nov 09 '10 at 23:17

You should be able to enter the debugger by clicking to the left of your macro text in the editor and placing a red dot on the line

For counter = 2 To 20

Then you can step through your macro until you get to the error.

Alternatively you can add error handling to your macro

On Error Goto Failed

at the top and before the end sub add

Failed: 
'handle error here

"Object not found" is likely from the curCell.Text call (curCell is null, nothing, or invalid, so calling .Text on it is failing) or the ActiveSheet.Cells call (not sure if this can happen)

Excel VBA Looping through cells and replacing their values

3 Answers3