CODEWITHSUNDEEP
excelexcel-2010vba
Andrew Lauer Barinov
Andrew Lauer Barinov

Reputation: 5754

Excel VBA Looping through cells and replacing their values

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

Upvotes: 1

Views: 4687

Answers (3)

Joel Spolsky
Joel Spolsky

Reputation: 33637

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.

Upvotes: 2

Dick Kusleika
Dick Kusleika

Reputation: 33145

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")

Upvotes: 6

Michael Pryor
Michael Pryor

Reputation: 25336

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)

Upvotes: 0

Related Questions