Luis Diaz
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-exchan ge.com/que stions/291 32606
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.
I have the following procedure which allows me to add specific string at the beginning or end a range.
https://www.experts-exchan
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
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.
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.
Thank you.
Give this a try. You can enter multiple column letters when prompted, separated by COMMAs.
»bp
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
»bp
ASKER
Thank you very much Bill possible to add the string as of row 2 instead of row1?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok, I adjusted,
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?
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
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?
ASKER
@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?
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
ASKER
Bill, I am sorry and the procedure works with empty rows as expected.
Thank you again for your help.
Thank you again for your help.
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:
Open in new window