Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

Excel VBA: Add specific value at the beginning or at the end of a column

Hello experts,

I have the following procedures which allows me to add specific strings in first & last position of a column values.

Sub Add_Specific_Char_First_Position()
Dim rng As Range
Dim strSpecificChar As String
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long

    strCol = InputBox("Please enter the column letter in which you add specific string:", "Choose Column Letter")

    lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row

    Set rng = Application.InputBox("Please select the cell in which you want to add the specific character:", "Select cell", Type:=8)

    If Not rng Is Nothing Then
    
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Cells(lngRow, strCol).Value =  strSpecificChar & Cells(lngRow, strCol).Value
        Next

    End If
    
End Sub

Open in new window


Sub Add_Specific_Char_Last_Position()
Dim rng As Range
Dim strSpecificChar As String
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long

    strCol = InputBox("Please enter the column letter in which you add specific string:", "Choose Column Letter")

    lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row

    Set rng = Application.InputBox("Please select the cell in which you want to add the specific character:", "Select cell", Type:=8)

    If Not rng Is Nothing Then
    
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
        Next

    End If
    
End Sub

Open in new window



I would like to merge those procedures in one procedure by adding input boxs and if conditions.
1-Inputbox ("Please report column letter in which you want to apply procedure")
2-Inputbox (“Please report value related to the action that you want to perform: 1 for adding string at the beginning else at the end”)
3-Inputbox(“Please select the cell in which is reported value that you want to add”)
Thank you in advance for your help.
ASKER CERTIFIED SOLUTION
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz

ASKER

Thank you, unable to test it right now. I will keep you informed.
Tested and it works. Possible to add an additional Case if  the reported value is different than 1 or 2? This case will exit sub with a MsgbBox. "Unable to proceed as value reported is different than 1 or 2").
Thank you again for your help.
In the existing code in the workbook I uploaded you'll find this:
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select

Open in new window


Change line 4 to suit your needs.
Tested and it works. Thank you very much.
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)