Need help creating a macro

[lantros79]

Hi Everyone! I need help creating a macro that does the following:

• Copy column I and paste the values there
• In column I, If there is a space before “OZ”, “PK”, or “CT”, delete it
• If the W column has “OG” in it, type “OG” at the beginning of the cells in column F

Thanks for your help!

 

[onlyadrafter]

Hello,

does this work as expected?

Sub COLUMN_I()
    Application.ScreenUpdating = False
    With Columns(9)
        .Copy
        .Range("I1").PasteSpecial (xlPasteValues)
        .Replace What:=" OZ", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" PK", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" CT", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
    End With
    For MY_ROWS = 1 To Range("I" & Rows.Count).End(xlUp).Row
        If Range("W" & MY_ROWS).Value = "OG" Then
            Range("F" & MY_ROWS).Value = "OG" & Range("F" & MY_ROWS).Value
        End If
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub

 

[lantros79]

Hi, thanks for the awesome quick response! Everything worked except the "OG" going to the beginning of the cell in column F. Basically, if "OG if anywhere in column W, and column F has "TEST" in it, I want that cell in column F to read:
OG TEST

Sorry, I wasn't super clear on that one

Hello,

does this work as expected?

Sub COLUMN_I()
    Application.ScreenUpdating = False
    With Columns(9)
        .Copy
        .Range("I1").PasteSpecial (xlPasteValues)
        .Replace What:=" OZ", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" PK", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" CT", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
    End With
    For MY_ROWS = 1 To Range("I" & Rows.Count).End(xlUp).Row
        If Range("W" & MY_ROWS).Value = "OG" Then
            Range("F" & MY_ROWS).Value = "OG" & Range("F" & MY_ROWS).Value
        End If
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub

 

[onlyadrafter]

Hello,

Sub COLUMN_I()
    Application.ScreenUpdating = False
    With Columns(9)
        .Copy
        .Range("I1").PasteSpecial (xlPasteValues)
        .Replace What:=" OZ", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" PK", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" CT", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
    End With
    For MY_ROWS = 1 To Range("I" & Rows.Count).End(xlUp).Row
        If Range("W" & MY_ROWS).Value Like "*OG*" Then
            Range("F" & MY_ROWS).Value = "OG " & Range("F" & MY_ROWS).Value
        End If
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub

this will find OG anywhere in column W ie, "OG", "COG", "COGS", "OGS" and will add OG to columns F.

 

[lantros79]

Awesome! This works great Thank you, have a great day!

Hello,

Sub COLUMN_I()
    Application.ScreenUpdating = False
    With Columns(9)
        .Copy
        .Range("I1").PasteSpecial (xlPasteValues)
        .Replace What:=" OZ", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" PK", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
        .Replace What:=" CT", Replacement:="OZ", LookAt:=xlPart, _
                SearchOrder:=xlByRows, MatchCase:=False
    End With
    For MY_ROWS = 1 To Range("I" & Rows.Count).End(xlUp).Row
        If Range("W" & MY_ROWS).Value Like "*OG*" Then
            Range("F" & MY_ROWS).Value = "OG " & Range("F" & MY_ROWS).Value
        End If
    Next MY_ROWS
    Application.ScreenUpdating = False
End Sub

this will find OG anywhere in column W ie, "OG", "COG", "COGS", "OGS" and will add OG to columns F.