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

asked on

VBA simplify code replace

I have a very complicated code that I think could be simplified.
For Column BB,
if "Fedex Letter" replace with  "Letter"
if "Fedex Pak" replace with  "Pak"
if anything else replace with "Box"

Can anyone help simplify?
Thanks!!

Sub ReplacePackages()


    Dim i As String
    Dim k As String
    Dim j As String
    Dim l As String
    Dim b As String
    Dim a As String
    Dim c As String
    Dim d As String
    Dim e As String
    Dim f As String
    Dim m As String
    Dim n As String
    Dim o As String
    Dim p As String
    Dim q As String
    Dim r As String
    
    a = "Customer Packaging"
    b = "Box"
    c = "Fedex Letter"
    d = "Letter"
    j = "Fedex Tube"
    l = "Box"
    e = "Fedex Pak"
    f = "Pak"
    i = "Fedex Box"
    k = "Box"
    r = "FedEx Large Box"
    m = "FedEx Small Box"
    n = "Fedex 25kg Box"
    o = "FedEx Medium Box"
    p = "Fedex 10kg Box"
    q = "Fedex 25kg Box"




    
    Columns("BB").Replace What:=i, replacement:=k, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=j, replacement:=l, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=e, replacement:=f, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=a, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=c, replacement:=d, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=r, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=m, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=n, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=o, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=p, replacement:=b, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace What:=q, replacement:=b, LookAt:=xlPart, MatchCase:=False
End Sub

Open in new window

Avatar of Ryan Chong
Ryan Chong
Flag of Singapore image

try:

Sub ReplacePackages()
    Dim i As String
    Dim k As String
    Dim j As String
    Dim l As String
    Dim b As String
    Dim a As String
    Dim c As String
    Dim d As String
    Dim e As String
    Dim f As String
    Dim m As String
    Dim n As String
    Dim o As String
    Dim p As String
    Dim q As String
    Dim r As String
    
    a = "Customer Packaging"
    b = "Box"
    c = "Fedex Letter"
    d = "Letter"
    j = "Fedex Tube"
    l = "Box"
    e = "Fedex Pak"
    f = "Pak"
    i = "Fedex Box"
    k = "Box"
    r = "FedEx Large Box"
    m = "FedEx Small Box"
    n = "Fedex 25kg Box"
    o = "FedEx Medium Box"
    p = "Fedex 10kg Box"
    q = "Fedex 25kg Box"
    
    Application.ScreenUpdating = False
    
    Columns("BB").Replace what:=c, Replacement:=d, LookAt:=xlPart, MatchCase:=False
    Columns("BB").Replace what:=e, Replacement:=f, LookAt:=xlPart, MatchCase:=False
    Range("BB2:BB20000").Select
    Range("BB2:BB20000").AutoFilter Field:=53, Criteria1:= _
        "<>*" & d & "*", Operator:=xlAnd, Criteria2:="<>*" & f & "*"
    Selection.FormulaR1C1 = l
    Range("BB1:BB20000").AutoFilter Field:=53
    
    Application.ScreenUpdating = True
End Sub

Open in new window

maybe something like this

Option Explicit

Sub ReplacePackages()
'if "Fedex Letter" replace with  "Letter"
' if "Fedex Pak" replace with  "Pak"
' if anything else replace with "Box"
    Dim iX As Integer
    Dim sCrit As String, sReplace


    sCrit = Choose(iX, "Fedex Letter", "Fedex Pak")
    sReplace = Choose(iX, "Letter", "Pak")

    With ActiveSheet
        If Not .AutoFilterMode Then .Range("BB1").AutoFilter

        For iX = 1 To 3
            .Range("BB1").AutoFilter Field:=53, Criteria1:=sCrit
            On Error Resume Next
            .Range(.Cells(1, 53), .Cells(.Rows.Count, _
                                         53).End(xlUp)).SpecialCells(xlCellTypeVisible).Value = sReplace
            On Error GoTo 0
        Next iX

        .Range("BB1").AutoFilter Field:=1, Criteria1:="<>Letter", _
                                 Operator:=xlAnd, Criteria2:="<>Pak"
        .Range(.Cells(1, 53), .Cells(.Rows.Count, _
                                     53).End(xlUp)).SpecialCells(xlCellTypeVisible).Value = "Box"
        .Range("BB1").AutoFilter
    End With

End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India 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