PDA

View Full Version : [SOLVED] VBA Compile Error "Else without if"



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

mikerickson
04-11-2017, 07:53 AM
You need to add
Next Rng above the offending Else.

Then the line "Msg" will error.

Logit
04-11-2017, 08:08 AM
.
.
See if this works for you:



Option Explicit


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
Dim xTitleID 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, Position) & Txt & Mid(Rng.Value, Len(Position) + 1, Len(Rng.Value) - Len(Position))
Next
End If
If 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)
Next
End If
End Sub

Zakolka
04-12-2017, 01:38 AM
Thank You Logit!

works perfectly now! :)

Logit
04-12-2017, 07:47 AM
You are welcome. Thank you !

Cheers :hi: