Euro5
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!!
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window