We help IT Professionals succeed at work.

Excel VBA:  retain values on a column based on string with 2 modes

Hello experts,

Following the questions resolved at:
I was wondering if we can cover the new requirement:
Have two modes modulated by an inputbox which allows me to retain prefix from left to right as is and another mode to retain prefix from right to left.

I attached the current procedure and the expected result for mode 1 and 2.

Thank you for your help.
Prefix_String.xlsm
Comment
Watch Question

Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
I assume this is in relation to this prior question?


Can you explain a bit more about the new "right to left" option?  In the case of your example file that you attached, what string would you have entered to produce the results you want in column C?  Would it still be "test", and therefore you want to take everything from the LEFT up to and including the string entered in the prompt?  Or something else?


»bp
Test your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016
Commented:
Assuming my assumption was correct, give this a try.

Option Explicit

Sub Prefix_Retention()
    
    Dim strIO As String
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim intPos As Integer
    Dim strPrefix As String
    Dim strColInput As String
    Dim strColOutput As String
    Dim intPrefixLen As Integer
    Dim strMode As String
    
    On Error GoTo Error_Routine
    
    strIO = InputBox("Please enter the input and output columns separated by a colon for the data in the form 'InputColum:OutputColumn'", "Choose Columns", "A:B")

    strColInput = Split(strIO, ":")(0)
    strColOutput = Split(strIO, ":")(1)

    If strColInput = vbNullString Or strColOutput = vbNullString Then
        MsgBox "Unable to proceed, please properly report required information", vbCritical
        Exit Sub
    End If
    
    If IsNumeric(strColInput) Then
        strColInput = Split(Cells(1, CInt(strColInput)).Address, "$")(1)
    End If
    
    If IsNumeric(strColOutput) Then
        strColOutput = Split(Cells(1, CInt(strColOutput)).Address, "$")(1)
    End If
    
    lngLastRow = Range(strColInput & "1048576").End(xlUp).Row
    If lngLastRow = 1 Then
        MsgBox "The column you selected as the input column does not contain any data", vbCritical
        Exit Sub
    End If
    
    strPrefix = InputBox("Please enter the starting character(s) of the data that you want to retain.", "Choose Prefix")
    If strPrefix = vbNullString Then
        MsgBox "Unable to proceed, please properly report required information", vbCritical
        Exit Sub
    End If
    intPrefixLen = Len(strPrefix)

    strMode = InputBox("Please enter the scanning mode (R=Right to left, L=Left to right", "Choose Mode")
    If strMode = vbNullString Then
        MsgBox "Unable to proceed, please properly report required information", vbCritical
        Exit Sub
    End If

    ' Validate input
    strMode = UCase(strMode)
    If strMode <> "R" And strMode <> "L" Then
        MsgBox "Unable to proceed, please properly report required information", vbCritical
        Exit Sub
    End If

    For lngRow = 2 To lngLastRow
        intPos = InStr(1, UCase(Cells(lngRow, strColInput).Value), UCase(strPrefix))
        If intPos > 0 Then
            If strMode = "R" Then
                Cells(lngRow, strColOutput).Value = Trim(Mid$(Cells(lngRow, strColInput).Value, intPos))
            Else
                Cells(lngRow, strColOutput).Value = Trim(Mid$(Cells(lngRow, strColInput).Value, 1, intPos + intPrefixLen - 1))
            End If
        End If
    Next

    Exit Sub

Error_Routine:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Prefix_Retention of Module Module1"
    
End Sub

Open in new window


»bp
Luis DiazIT consultant

Author

Commented:
Bill,

Would it still be "test", and therefore you want to take everything from the LEFT up to and including the string entered in the prompt?

Yes the string reported was test. The idea is to have two modes: 1 take the string reported from right to left and 2 take the string reported from left to right including the string reported.

Just one question prior to test. Should I report exactly the string in upper or lower case on the basis of string in the cell.
Example
I have in cell A2 123Id456 in order to get 123Id with mode 1 or Id456 with mode 2 should I report Id in the inputbox or can I report ID or id?

Thank you for your help.
Bill PrewTest your restores, not your backups...
SILVER EXPERT
Expert of the Year 2019
Top Expert 2016

Commented:
Your starting code that you posted ignored case, so that is true of the new change as well.
Luis DiazIT consultant

Author

Commented:
Thank you very much Bill!
I tested and it works! Already added to my Add-in file.

Thank you again!

Regards,
Luis.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.