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?
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?
0
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.
0
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

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy 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
0
Martin LissOlder than dirtCommented:
With that workbook the code you originally posted in this question works for me.
0
Martin LissOlder than dirtCommented:
After running the code.
0
Euro5Author Commented:
Martin, You are right! When I run in the sample, it worked.
When I run in the actual....


Zone
0
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
0
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
0
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.
0

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...
0
Euro5Author Commented:
Super, that worked! Thanks again! ;)
0
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
0
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.