Solved: VBYesNo Help

[mangisqa]

I am having issues ending my following code and keep getting End with or Block If errors.

I am trying to have a message box pop up if a value is found and if they click yes- then the list is sorted to show that value. If they click no, it simply disappears




Dim intRows As Integer
If Target.Column = 9 Then 'Error check only fires if Column 9 (I) is edited, else exit sub
For intRows = 5 To 17000 'Error check only fires if Rows 5 to 47 are edited, else exit sub
If Cells(intRows, Target.Column) = "New Cast Member" Then
MsgBox "You have New Cast Members. Do you want to view New Cast Members", vbYesNo, "[New Cast Member Alert]"
If intResponse = vbYes Then
ActiveSheet.Range("$A$4:$K$17000").AutoFilter Field:=9, Criteria1:="New Cast Member"
ActiveWorkbook.Worksheets("Paste Full Report Here").AutoFilter.Sort.SortFields. _
Clear
ActiveWorkbook.Worksheets("Paste Full Report Here").AutoFilter.Sort.SortFields. _
Add Key:=Range("A4:A17000"), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortTextAsNumbers
With ActiveWorkbook.Worksheets("Paste Full Report Here").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End If
Else: Exit Sub
End Sub






Thanks for any help!

[Kenneth Hobs]

You have too many Ifs in it. When coding, use Structure so you can see where a loop starts and where it ends.

e.g.
With Range("A1")
MsgBox .Value
MsgBox .Address
End With

You did not put your intResponse on the left side of the MsgBox. When you do, it should look something like:
intResponse = MsgBox ("You have New Cast Members. Do you want to view New Cast Members", vbYesNo, "[New Cast Member Alert]")

[mangisqa]

Thanks, I am very new to this and don't really understand your comment on structure. I basically found an expample online and tried to manipulate it.

Can you please show me what the code looks like in the structured format?

Thanks!

[Kenneth Hobs]

I showed structure which is simply indentation, usually tabs, in the first code snippet. Example 2:
Dim i as Integer
For i = 1 to 3
MsgBox "Hello Message: " & i
If i = 2 then
MsgBox "Half way done!"
End If
Next i

Since I only have one statement for the If() I could have just put it all on one line and left off the End If.

[mangisqa]

Thanks for the explanation! Like I said I am new!