vba error stopping "replace" code

I get an error code on line 6 - at Replace.

Sub Replace_Zones2()

    Dim cell As Range
    For Each cell In Range([J2], Cells(Rows.Count, "J").End(xlUp))
        If cell = "US" Then
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "A", "2")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "B", "3")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "C", "4")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "D", "5")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "E", "6")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "F", "7")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "G", "8")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "H", "9")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "I", "10")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "J", "11")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "K", "12")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "L", "13")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "M", "14")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "N", "15")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "O", "16")
        Else
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "A", "2")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "C", "3")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "D", "4")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "E", "5")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "F", "6")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "G", "7")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "H", "8")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "I", "9")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "J", "10")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "K", "11")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "L", "12")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "M", "13")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "N", "14")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "O", "15")
            Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "P", "16")
        End If
    Next
    
End Sub

Open in new window

Euro5Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GrahamSkanRetiredCommented:
What is the error message, please?
Martin LissOlder than dirtCommented:
Do any of the cells change before you get the error?

When you get the error highlight Cells(cell.Row, "C") and let us know the value.
Martin LissOlder than dirtCommented:
If your aim is to change every cell in column C based on the value in J2 and if the values in C where more than one character long, then your old code would work if you changed all the xlWhole to xlPart as shown below. If this doesn't work then please supply a sample workbook.

Sub Replace_Zones()

    If Cells(2, "J") = "US" Then
    
        Columns("C").Replace What:="A", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="B", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="C", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
            Columns("C").Replace What:="D", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="E", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        Columns("C").Replace What:="F", Replacement:="7", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="G", Replacement:="8", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="H", Replacement:="9", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="I", Replacement:="10", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="J", Replacement:="11", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
          Columns("C").Replace What:="K", Replacement:="12", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="L", Replacement:="13", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="M", Replacement:="14", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="N", Replacement:="15", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
          Columns("C").Replace What:="O", Replacement:="16", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
    Else
    
        Columns("C").Replace What:="A", Replacement:="2", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="C", Replacement:="3", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="D", Replacement:="4", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
            Columns("C").Replace What:="E", Replacement:="5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="F", Replacement:="6", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
        Columns("C").Replace What:="G", Replacement:="7", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="H", Replacement:="8", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="I", Replacement:="9", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="J", Replacement:="10", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="K", Replacement:="11", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
          Columns("C").Replace What:="L", Replacement:="12", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="M", Replacement:="13", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="N", Replacement:="14", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
         Columns("C").Replace What:="O", Replacement:="15", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
        
          Columns("C").Replace What:="P", Replacement:="16", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
End If

End Sub

Open in new window

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Euro5Author Commented:
Martin,
What is happening is that they are all changing from D to 5 (CA should be changing to 4).
Also, it is changing the header name - which should not happen.
test-for-zones.xlsm
Martin LissOlder than dirtCommented:
With that workbook the code you originally posted in this question works for me.
Martin LissOlder than dirtCommented:
After running the code.
Euro5Author Commented:
Martin, You are right! When I run in the sample, it worked.
When I run in the actual....


Zone
Euro5Author Commented:
Martin,
Here is a sample from the actual (reduced size), where the error is happening.
Can you help identify the issue? I'm stuck,....
test-for-zones.xlsm
Martin LissOlder than dirtCommented:
What version of Excel are you using for the "actual"? If you type "Replace" and then press the space bar, do you see a yellow line like in this picture? If so what does it say?Intellisense
Martin LissOlder than dirtCommented:
You have a conflict in your References. In Replace_Zones2 change Replace to VBA.Replace everywhere it's used and it should work.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Euro5Author Commented:
Martin,
I am trying this and it is running...
Euro5Author Commented:
Super, that worked! Thanks again! ;)
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.