Zakolka
04-11-2017, 07:18 AM
Hi Guys,
trying to make this thing work, but getting an error.
Can you pls take a look?
Sub Enter_Text()
'Setting Variables
Dim Rng As Range
Dim WorkRng As Range
Dim Txt As String
Dim Location As String
Dim Position As Integer
Dim Msg As String
On Error Resume Next
'Defining Variables
xTitleID = "Macro"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select a Range", xTitleID, WorkRng.Address, Type:=8)
Txt = Application.Selection
Txt = Application.InputBox("Enter Desired Text", xTitleID, Type:=2)
Location = Application.Selection
Location = Application.InputBox("Type whether you want to add text from LEFT or RIGHT", xTitleID, Type:=2)
Position = Application.Selection
Position = Application.InputBox("Enter Text position with amount of digits", xTitleID, Type:=1)
Msg = MsgBox("Please check your criterias and Try Again", , xTitleID)
'Setting Output
If Location = "LEFT" Then
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, Postion) & Txt & Mid(Rng.Value, Len(Postion + 1), Len(Rng.Value) - Len(Postion))
ElseIf Location = "RIGHT" Then
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, Len(Rng.Value) - Len(Right(Rng.Value, Position))) & Txt & Right(Rng.Value, Position)
Else
Msg
End If
Next
End Sub
trying to make this thing work, but getting an error.
Can you pls take a look?
Sub Enter_Text()
'Setting Variables
Dim Rng As Range
Dim WorkRng As Range
Dim Txt As String
Dim Location As String
Dim Position As Integer
Dim Msg As String
On Error Resume Next
'Defining Variables
xTitleID = "Macro"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Select a Range", xTitleID, WorkRng.Address, Type:=8)
Txt = Application.Selection
Txt = Application.InputBox("Enter Desired Text", xTitleID, Type:=2)
Location = Application.Selection
Location = Application.InputBox("Type whether you want to add text from LEFT or RIGHT", xTitleID, Type:=2)
Position = Application.Selection
Position = Application.InputBox("Enter Text position with amount of digits", xTitleID, Type:=1)
Msg = MsgBox("Please check your criterias and Try Again", , xTitleID)
'Setting Output
If Location = "LEFT" Then
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, Postion) & Txt & Mid(Rng.Value, Len(Postion + 1), Len(Rng.Value) - Len(Postion))
ElseIf Location = "RIGHT" Then
For Each Rng In WorkRng
Rng.Value = VBA.Left(Rng.Value, Len(Rng.Value) - Len(Right(Rng.Value, Position))) & Txt & Right(Rng.Value, Position)
Else
Msg
End If
Next
End Sub