Nested IFs

[BudsSpud]

Hoping someone can help! First time doing Excel macros, have copied logic from the web and it worked fine however I need to expand it. Here goes ...

Spreadsheet column E has data validation from a list of 5 text values. If E is "Misrouted", I want to prompt for text in F and G. If E is "Other", I want to prompt for text in H.

What I copied from the web got me to prompt for text in F. I haven't had success prompting for text in G and H.

What I am attempting is below. I receive a Do without Loop error when I step into the line highlighted.

Any guidance is appreciated! And yes, I am a newbie to macros ... :yes

Thanks in advance.

++++++++++++++++++++++++++++++++++++++++++++++++++++
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If


Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("E2:E5000"))
If isect Is Nothing Then


ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter team in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Team to Route to?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub


ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter reason for team in " & Target.Offset(0, 2).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Why?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub


ElseIf Target.Value = "Other" Then
com = "Enter Other details in " & Target.Offset(0, 3).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Other details", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1


Else
Target.Offset(0, 1).Value = "" 'Remove this line if not desired


End If
End Sub

[arnelgp]

firstly you Subroutine declaration is incorrect:


Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Rows.Count > 1 Or Target.Columns.Count > 1 Then
Exit Sub
End If


Dim com As String
Dim comm1 As String
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("E2:E5000"))
If isect Is Nothing Then


ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter team in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Team to Route to?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub


ElseIf Target.Value = "Misrouted Ticket" Then
com = "Enter reason for team in " & Target.Offset(0, 2).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Why?", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub


ElseIf Target.Value = "Other" Then
com = "Enter Other details in " & Target.Offset(0, 3).Address(RowAbsolute:=False, columnabsolute:=False)


Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Other details", Type:=2)
On Error GoTo myloop
If comm1 = False Then
comm1 = ""
Exit Sub
End If
Loop
End If
myloop:
On Error GoTo -1
Loop
Target.Offset(0, 1).Value = comm1


Else
Target.Offset(0, 1).Value = "" 'Remove this line if not desired

End If
Loop
End If

End Sub

[BudsSpud]

Appreciate the response. Not sure what to do with this. :wot

[arnelgp]

make a copy of your workbook and put it in a safe place.
copy the code i gave you overwritting your code.

you made a backup so in case it won't work, you can
reinstate your backup.

[p45cal]

firstly you Subroutine declaration is incorrect:How do you know?!
BudSpud's code is from the single sheet's code-module; perhaps he wants it to work only on that sheet?[Private Sub Worksheet_Change(ByVal Target As Range)]
arnelgp's code is designed to go into the ThisWorkbook's code module and will work on all sheets in the workbook.[Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)]

Perhaps this in the sheet concerned's code-module:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim com As String, comm1 As Variant

If Target.Count <> 1 Then Exit Sub
'Specify the range below. Set single column range else the code will error out
Set isect = Application.Intersect(Target, Range("E2:E5000"))
If Not isect Is Nothing Then
Select Case Target.Value
Case "Misrouted Ticket"
com = "Enter team in " & Target.Offset(0, 1).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Team to Route to?", Type:=2)
If comm1 = False Then Exit Sub
Loop
Target.Offset(0, 1).Value = comm1
com = "Enter reason for team in " & Target.Offset(0, 2).Address(RowAbsolute:=False, columnabsolute:=False)
comm1 = ""
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Why?", Type:=2)
If comm1 = False Then Exit Sub
Loop
Target.Offset(0, 2).Value = comm1
Case "Other"
com = "Enter Other details in " & Target.Offset(0, 3).Address(RowAbsolute:=False, columnabsolute:=False)
Do While comm1 = ""
comm1 = Application.InputBox(prompt:=com, Title:="Other details", Type:=2)
If comm1 = False Then Exit Sub
Loop
Target.Offset(0, 3).Value = comm1
End Select
End If
End Sub