Automated Edit/Replace

[jbfrank]

I'm trying to find a way to automate the Edit --> Replace function in Excel.

Every morning, I pull a report containing well over a hundred different SKU's. Each SKU has it's own suffix code. For example, let's say a package of golf balls has 35600 as the SKU. At the end of that SKU, we might have a /05 to designate pink golf balls rather than white (which would have an /01. With the suffix, the entire SKU would look like this: 35600/01.

I would like to remove the /01 from each of the SKU's. If I create a list in Excel of all of the slash codes available, how do I get the Edit --> Replace function to automate so that every slash code in that list is removed from a set column?

Thank you in advance for your help.

 

[fridgenep]

Hi jbfrank,

in column G are your SKUs and in Range H1:H150 are your suffix codes....

Please try this macro in a test file:

Option Explicit

Sub test()
    Dim lngI    As Long
    Dim lngLast As Long
    Dim rng     As Range

 Application.ScreenUpdating = False
 lngLast = ActiveSheet.Range("G65536").End(xlUp).Row
 
 For Each rng In ActiveSheet.Range("H1:H150")
    For lngI = 1 To lngLast
        If rng.Value = Right(Cells(lngI, 7), Len(rng.Value)) Then
          With Cells(lngI, 7)
            .Value = Left(.Value, Len(.Value) - Len(rng.Value))
            .Interior.ColorIndex = 3
          End With
        End If
    Next lngI
 Next rng
 Application.ScreenUpdating = True
 MsgBox "Ready!"
End Sub