Turn a function into a subprocess -- STUCK

Question

Column 'P' ("P6:P3000") holds a value as such "EMPLOYEE_CONTRACT_STATUS_Closed". I am trying to pull the "Closed" (could also be "Open") portion out of the cell into column 'Q' or just replace the existing column 'P' value with the last text after the delimiter ("_")... "EMPLOYEE_CONTRACT_STATUS_Closed" --> "Closed" or "Open." This creates these steps:

Create new column Q
Insert new value in column header
Perform function in 'P' to either replace values or dump into column 'Q' ("Q6:Q3000")

Below I have what I have so far --> Code to create column and to call a function code to pull the last text after last delimiter... this is a part of an automated process so the goal is not to touch or manipulate any of the cell values. I know there is possibly for a Subprocess to perform this but I cannot figure it out and keep scratching my head. This is my first time on the forum and for someone to supply a fixed code but also EXPLAIN the syntax behind it would be great because I am pretty experience with VBA, but have never ran into this process. THANKS ^_^

& 2. Creating new column and changing the header name:

Sub ContractStatus_Change()
    Application.ScreenUpdating = False
    Workbooks("DIV_EIB_Tool.xlsm").Worksheets("EIBMaintainEmployeeContractsW31").Range("Q5") _
        .EntireColumn.Insert , CopyOrigin:=xlFormatFromLeftOrAbove
    Worksheets("EIBMaintainEmployeeContractsW31").Range("Q5").Value = "Contract Status"
    Worksheets("EIBMaintainEmployeeContractsW31").Range("Q6:Q3000").NumberFormat = "General"
    Application.ScreenUpdating = True
End Sub

My function to pull last text out from disclosed value:

Function RightWord(r As Range) As Variant
    Dim s As String
    s = Trim(r.Value)
    RightWord = Mid(s, InStrRev(s, "_") + 1)
End Function

I have not run into an error yet, just do not know how to piece this together, under assumption I can probably run this all through one sub process but I am having a massive brain fart.

Possible duplicate of [Excel: last character/string match in a string](https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string) — GSerg, Jul 12 '19 at 12:57

YasserKhalil · Accepted Answer · 2019-07-12T14:02:46.417

Try this code

Sub Test()
Dim a, i&

With Worksheets("EIBMaintainEmployeeContractsW31")
    .Columns("Q").Insert
    a = .Range("P6:P" & .Cells(Rows.Count, "P").End(xlUp).Row).Resize(, 2).Value

    For i = LBound(a) To UBound(a)
        If InStr(a(i, 1), "_") Then
            a(i, 2) = Split(a(i, 1), "_")(UBound(Split(a(i, 1), "_")))
        End If
    Next i

    With .Range("Q5")
        .Value = "Contract Status"
        .Offset(1, -1).Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End With
End With
End Sub

I started the code by dealing with the sheet EIBMaintainEmployeeContractsW31 so between With and End With you will notice some lines start with dot which refers to this worksheet. Then insert a column before column Q and stored the required range which is P6 to P & last row into an array (arrays are faster) After that looping the array which holds two columns (one for the raw data and the other for the required output). Make sure of underscore existence using InSstr function then if it exists store into the second column the last part of the split output based on the underscore. Finally populating the array into the worksheet. Hope that explanation helps you.

You are a rock star, thank you. Could you by chance explain the logic on this? — AlmondButterJoy, Jul 12 '19 at 13:09
@AntyA You're welcome. I have added some explanation to the post. — YasserKhalil, Jul 12 '19 at 14:03

Turn a function into a subprocess -- STUCK

1 Answers1