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

asked on

Excel VBA: add string in multiple columns

Hello experts,

I have the following procedure which allows me to add specific string at the beginning or end a range.
https://www.experts-exchange.com/questions/29132606

Sub Add_Specific_Char()
Dim rng As String
Dim strSpecificChar As Variant 'New declaration in order to add numeric and non numeric values
Dim strCol As String
Dim lngLastRow As Long
Dim lngRow As Long
Dim intWhich As Integer
    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    
    If intWhich_temp = vbNullString Then
     MsgBox ("No input!")
     Exit Sub
    End If
    If IsNumeric(intWhich_temp) Then
        intWhich = intWhich_temp
        End If
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
    strCol = InputBox("Please report column letter in which you want to apply procedure", "Choose Column Letter")
        If strCol = vbNullString Then
     MsgBox ("No input!")
     Exit Sub
    End If
    temp_rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)
        If VarType(temp_rng) = vbBoolean Then
     MsgBox ("No input!")
     Exit Sub
     Else
     If VarType(temp_rng) <> vbString Then
     MsgBox ("Cell not a string")
     Exit Sub
     End If
     rng = temp_rng
    End If
    lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Select Case intWhich
                Case 1
                    Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
                Case 2
                    Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
            End Select
        Next
End Sub

Open in new window


I would like to bring an enhancement.
Instead of applying the procedure in a single column I would like to apply in different columns.
The idea would be to ask in InputBox: Please select ranges in column(s) in which you want to apply the procedure.
If you have questions, please contact me.
Avatar of Fabrice Lambert
Fabrice Lambert
Flag of France image

Well, to achieve this, you'll need to make SRP compliant functions (in other words: Split your current function into several ones with a single responsibility each):
One function to prompt user for inputs.
One function to apply the prompted string to one column.
One function to do the logic (prompt / apply to all columns).
Your code will be more flexible, stable, re-usable, understandable, easyer to debug.

Something like:
Public Sub Main()
        '// variables to gather user input
    Dim str As String              '// prompted string
    Dim beginning As boolean       '// beginning or end of cell indicator
    Dim Cols as Excel.Range        '// prompted columns

        '// Prompt user input
    If(GetUserInput(str, beginning, columns)) Then
            '// If Ok, loop trough all columns and apply str.
        Dim Col As Excel.Range
        For Each col in Cols.Columns
            Add_Specific_Char Col, str, beginning
        Next
    End If
End Sub

Private Function GetUserInput(ByRef str As String, ByRef beginning As Boolean, ByRef columns As Excel.Range) As Boolean
        '// code prompting user and initialising output parameters here
End Function

Private Sub Add_Specific_Char(ByRef Col as Excel.Range, byVal str As String, ByVal beginning As Boolean)
        '// Code adding str to the beginning or end of Col here
End Sub

Open in new window

Avatar of Luis Diaz

ASKER

Ok, thank you, could you please help me to get up the single procedure or function to stock one of either columns range based on selection?An option will be to use semicolon delimiter to apply the procedure in multiple columns.
Thank you.
Avatar of Bill Prew
Bill Prew

Give this a try.  You can enter multiple column letters when prompted, separated by COMMAs.

Option Explicit

Sub Add_Specific_Char()
    Dim rng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant 'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
        
    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    
    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    If IsNumeric(intWhich_temp) Then
        intWhich = intWhich_temp
    End If
    
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
    
    strColList = InputBox("Please report column letter(s) in which you want to apply procedure", "Choose Column Letter(s)")
    If strColList = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    temp_rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)
    If VarType(temp_rng) = vbBoolean Then
        MsgBox ("No input!")
        Exit Sub
    Else
        If VarType(temp_rng) <> vbString Then
            MsgBox ("Cell not a string")
            Exit Sub
        End If
        rng = temp_rng
    End If
    
    For Each strCol In Split(strColList, ",")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        strSpecificChar = rng
        
        For lngRow = 1 To lngLastRow
            Select Case intWhich
                Case 1
                    Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
                Case 2
                    Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
            End Select
        Next
    Next
    
End Sub

Open in new window


»bp
Thank you very much Bill possible to add the string as of row 2 instead of row1?
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew

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
Ok, I adjusted,

Sub Add_Specific_String_Multiple_Columns()
    Dim rng As String
    Dim temp_rng As String
    Dim strSpecificChar As Variant 'New declaration in order to add numeric and non numeric values
    Dim strCol As Variant
    Dim strColList As String
    Dim lngLastRow As Long
    Dim lngRow As Long
    Dim intWhich As Integer
    Dim intWhich_temp As String
        
    intWhich = 0
    intWhich_temp = InputBox("Please report value related to the action that you want to perform: 1 for adding string at the beginning 2 at the end")
    
    If intWhich_temp = vbNullString Then
        MsgBox ("No input!")
        Exit Sub
    End If
    
    If IsNumeric(intWhich_temp) Then
        intWhich = intWhich_temp
    End If
    
    Select Case intWhich
        Case 1, 2
        Case Else
            MsgBox "Please enter '1' or '2'"
            Exit Sub
    End Select
    
    strColList = InputBox("Please report column letter(s) following by ; in which you want to apply procedure," _
    & Example & ": 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
    
    temp_rng = Application.InputBox("Please select the cell in which is reported value that you want to add", "Select cell", Type:=8)
    If VarType(temp_rng) = vbBoolean Then
        MsgBox ("No input!")
        Exit Sub
    Else
        If VarType(temp_rng) <> vbString Then
            MsgBox ("Cell not a string")
            Exit Sub
        End If
        rng = temp_rng
    End If
    
    For Each strCol In Split(strColList, ";")
        lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
        strSpecificChar = rng
        
        For lngRow = 2 To lngLastRow
            Select Case intWhich
                Case 1
                    Cells(lngRow, strCol).Value = strSpecificChar & Cells(lngRow, strCol).Value
                Case 2
                    Cells(lngRow, strCol).Value = Cells(lngRow, strCol).Value & strSpecificChar
            End Select
        Next
    Next
    
End Sub

Open in new window


The only problem that I am having is when i reported columns that have empty cell in between, do you know how can we manage this?
@Martin I made always this mistake and I want to avoid, when I have a Microsoft Excel VBA question which category should I use?
Just Microsoft Excel?
The only problem that I am having is when i reported columns that have empty cell in between, do you know how can we manage this?

I don't think I understand your question.  You can put one or more columns in a comma delim list, like "B,D".  That would do the processing for all of column B, and then all of column D (excluding row 1).  Are you expecting something different?


»bp
Bill, I am sorry and the procedure works with empty rows as expected.
Thank you again for your help.