Excel VBA: Add specific value on multiple columns

 Sub Add_Specific_String_Multiple_Columns()

    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long, lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String

    intWhich = 0
    intWhich = Application.InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end", Type:=1)

    If intWhich = 0 Then
        MsgBox ("No input!")
        Exit Sub
    ElseIf intWhich > 2 Then
        MsgBox "Please enter '1' or '2' only."
        Exit Sub
    End If

    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
                          & ": A for single column A;C;D for multiple columns", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    For Each strCol In Split(strColList, ";")
        If Not ValidCellReference(strCol) Then
            MsgBox strCol & " is not a valid column letter.", vbExclamation
            Exit Sub
        End If
    Next strCol
    
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row

        For lngRow = 2 To lngLastRow
            Select Case intWhich
            Case 1
                Cells(lngRow, strCol).Value = lngRow - 1 & Cells(lngRow, strCol).Value
            Case 2
                Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & lngRow - 1
            End Select
        Next
    Next
End Sub

Function ValidCellReference(ByVal str As String) As Boolean
Dim rng As Range
On Error Resume Next
Set rng = Cells(1, str)
On Error GoTo 0
If Not rng Is Nothing Then ValidCellReference = True
End Function

Select allOpen in new window