asked on

VBA replace based on values in column J

The following code should replace the text based on the text in column J.
If it is "US", use one replace, if not, use another.
However, when i run it, it replaces all with the second condition - even in "US" is in column J, it uses the second set.
Can anyone help?

Sub Replace_Zones()

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

End Sub

Open in new window

Wayne Taylor (webtubbs)

Can you post a sample workbook to test? There is nothing obvious as to why it wouldn't work. It very clearly checks if cell J2 contains "US".

Euro5

ASKER

Wayne,
In the attached, the US should show 5 and the CA should show 4, both are D.
But they all show 5 when I run.
Any ideas?
Thanks!
test-for-zones.xlsm

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Euro5

ASKER

I understand - thanks so much! :)

Euro5

ASKER

Wayne,
I am getting wrong number of arguments or invalid property assignment error.
At Replace in line 6.
Cells(cell.Row, "C") = Replace(Cells(cell.Row, "C"), "A", "2")