Luis Diaz
asked on
Excel VBA: Add specific string on multiple columns v2
Hello experts,
The following procedure allows me to add specific string at the end or at the beginning:
I would like to revise the procedure as following:
1-Place 3rd inputbox in first position and instead of selecting a cell I would like to report directly the string to add.
If you have questions, please contact me.
Thank you for your help.
The following procedure allows me to add specific string at the end or at the beginning:
Sub Add_Specific_String_Multiple_Columns()
Dim sRng 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, lngRow As Long
Dim intWhich As Integer
Dim intWhich_temp As String
On Error GoTo Error_Routine
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
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," _
& ": 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
sRng = temp_rng
End If
For Each strCol In Split(strColList, ";")
lngLastRow = Range(strCol & Rows.Count).End(xlUp).Row
strSpecificChar = sRng
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
Exit Sub
Error_Routine:
MsgBox Err.Description, vbExclamation, "Something went wrong!"
End Sub
I would like to revise the procedure as following:
1-Place 3rd inputbox in first position and instead of selecting a cell I would like to report directly the string to add.
If you have questions, please contact me.
Thank you for your help.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are totally right. And the idea is to have the add string mechanism by column and add till the last used range of the column and not till last used range of one of the column. I will keep the previous version I hope this is fine with you. However I will also accept your both solutions as both solutions work!
You have two versions of the codes and you may use them as per your requirement.
ASKER
I tested but I am having a little issue.
When I report multiple columns: Example in the attached file: a;b;c. the procedure is just applied on column A. Could you please help me to revise the procedure?
Thank you for your help.
Dummy-file-add-string-multiple-colu.xlsx