Link to home
Start Free TrialLog in
Avatar of Euro5
Euro5Flag for United States of America

asked on

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

Avatar of GrahamSkan
GrahamSkan
Flag of United Kingdom of Great Britain and Northern Ireland image
What is the error message, please?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
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.
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
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

Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
With that workbook the code you originally posted in this question works for me.
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Martin, You are right! When I run in the sample, it worked.
When I run in the actual....


User generated image
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
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?User generated image
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Martin,
I am trying this and it is running...
Avatar of Euro5
Euro5
Flag of United States of America image

ASKER

Super, that worked! Thanks again! ;)
Avatar of Martin Liss
Martin Liss
Flag of United States of America image
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