If ElseIf and Else statements to call several subs

[markswjh]

Hi All

I am attempting to create a sub to perform one task if a cell contains a "Y" another if "N" and another if "". If it contains anything else it should call another sub.

I have the following code, but keep coming up with the same "else without if" error. I am a bit of an amateur, so any advice with my code would be great as well, but it would be really useful to be able to fix this error. Thanks

Will

Sub HWDataEntry()

'
' Homework Data Entry Macro
' This macro loops taking marks, adds them to the
' Homework sheet. Those who haven't submitted are
' added to the detentions list and any data that
' does not meet the value criteria is queried via
' message box
'
Dim usrinput As String
Dim iresponse As Integer
Dim answerinp As String

Sheets("Homework").Select
Range("B4").Select
Sheets("Submition Forms").Select
Range("C4").Select

Do

If ActiveCell.Value = "Y" Then
    ActiveCell.Copy
    Sheets("Homework").Select
    ActiveCell.Select
    Selection.PasteSpecial
    ActiveCell.Offset(1, 0).Select
    Sheets("Submition Forms").Select
    ActiveCell.Offset(1, 0).Select

ElseIf ActiveCell.Value = "N" Then
    ActiveCell.Copy
    Sheets("Homework").Select
    ActiveCell.Select
    Selection.PasteSpecial
    ActiveCell.Offset(1, 0).Select
    Sheets("Submition Forms").Select
    ActiveCell.Offset(1, 0).Select
    Call FmtDetentions
    Call DetentionListAddName

ElseIf ActiveCell.Value = "" Then
    End If
  
Else
    Call CorrectData
End If

Loop Until IsEmpty(ActiveCell)
    
 Sheets("Homework").Select
 Cells.Select
    Range("F3").Activate
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent1
        .TintAndShade = 0.799981688894314
        .PatternTintAndShade = 0
    End With

End Sub

 

[Micron]

Your indentation is better than none at all, but could be better, and that might be why you're not spotting the error. You've terminated the IF block then typed Else.

.....End If

Else
.....Call CorrectData
End If

Consider using Select Case block for multiple conditions.

 

[markswjh]

Ah! I was hoping that it would mean “elseif the value is blank end if, else call correctdata and then end if” how would you write this?

 

[Micron]

If I understand the want, then perhaps
ElseIf ActiveCell.Value = "" Then Exit Sub

Else
......Call CorrectData
End If
Consider

Do Until IsEmpty(ActiveCell)
    Select Case ActiveCell
       Case "Y"
         ActiveCell.Copy
         Sheets("Homework").Select
         ActiveCell.Select
         Selection.PasteSpecial
         ActiveCell.Offset(1, 0).Select
         Sheets("Submition Forms").Select
         ActiveCell.Offset(1, 0).Select

      Case "N" Then
         ActiveCell.Copy
         Sheets("Homework").Select
         ActiveCell.Select
         Selection.PasteSpecial
         ActiveCell.Offset(1, 0).Select
         Sheets("Submition Forms").Select
         ActiveCell.Offset(1, 0).Select
         Call FmtDetentions
         Call DetentionListAddName

      Case Else 'or Case "". The former for any other possibility, the latter for only ""
         Call CorrectData
    End Select
Loop

You don't have to Select sheets or ranges in order to do simple copy/paste actions. I suppose that changing that now would require a big re-write.

 

[markswjh]

Brilliant thanks!

I've just tried this and it works until it gets to the Case Else, as despite the fact it is in the Do until Isblank, Else seems to also consider "". So it still calls CorrectData. What I need it to do is when it sees blank it exits the loop but if it sees anything other than Y N or "" it will call CorrectData. I go back to my previous question of how do I exit the loop if it is blank but stay in the loop if it is not?

does that make sense?

Thanks again for all your help!

Will

 

[Micron]

Yes, Case Else will execute for anything that is not Y or N, which means "" is a Case Else condition that would be True. Also, if possible to have any entry not Y or N (e.g. "C") then you should code for that. My comments in the code were meant to say you had an option to use Case Else for anything, or Case "" . Not sure if Case "" is the correct syntax for a "" condition. If not let me know if that's a problem.

As to how to exit a loop, it is that simple to exit IF blocks or any loop AFAIK, and that is the Exit statement. Used like
Exit For
Exit Do
Exit Sub
Exit Function
(only one of those, and the correct one for the situation, of course). The only blocks I can think of at the moment that can't use Exit is Select Case and With blocks.

 

[Micron]

Update:
So maybe what you need is

      Case  ""
         Call CorrectData

      Case Else
          Msgbox "Some message goes here"
    End Select

or don't bother with the Else if you have that covered. If Case "" doesn't work, try Case Is = ""
Watch that empty cells don't get evaluated as 0 instead of "".

 

[markswjh]

Thanks for this! It might be easier to give a little context. This is a homework checker. So if the teacher enters Y it inputs the marks for the homework, if N it means that the student didn’t hand in homework, if it is blank that means that the list of students and homework must have finished so it should stop looping, and if it is anything else, the probably exhausted teacher has entered the wrong data, at which point it will run CorrectData, which is a message box to say that’s wrong try Y or N. Does that make sense? That is why I can’t define the other as specific cases as it could be anything.

If it worked (which it doesn’t) I would need something like:

Case  ""
         End Select

      Case Else
         Call CorrectData

End Select

But this doesn’t work because it thinks that the first end select means end select. What I want it to mean is “in the case, and only in the case, that the list finishes (or isblank) then end select, however if it is anything else, call CorrectData and then once all that is done, end select”

I hope that makes sense and again thank you!

 

[Micron]

I did mention that you can't exit a Select Case block with line of code. It simply exits after when the first condition is true.

if it is blank that means that the list of students and homework must have finished so it should stop looping

Not sure what you mean by that wrt the select block. You're saying it's the loop that isn't working? Or the case statements aren't working with what I posted last?

 

[markswjh]

I did mention that you can't exit a Select Case block with line of code. It simply exits after when the first condition is true.

I know I was just hoping there was a work around. Oh well!

if it is blank that means that the list of students and homework must have finished so it should stop looping

Not sure what you mean by that wrt the select block. You're saying it's the loop that isn't working? Or the case statements aren't working with what I posted last?

What I mean is that if the cell it is looking at (ActiveCell) is blank, that must mean that there is a gap in the list of data and the only reason there would be a gap in the list of data is if that list has finished. Therefore it should exit the loop, but if the cell is not blank, and does not contain a Y or an N (and therefore contains any other character), it will call CorrectData.

The

Public Sub Testitoutsub()

Do Until IsEmpty(ActiveCell)
    Select Case ActiveCell
       Case "Y"
         ActiveCell.Copy
         Sheets("Homework").Select
         ActiveCell.Select
         Selection.PasteSpecial
         ActiveCell.Offset(1, 0).Select
         Sheets("Submition Forms").Select
         ActiveCell.Offset(1, 0).Select

      Case "N"
         ActiveCell.Copy
         Sheets("Homework").Select
         ActiveCell.Select
         Selection.PasteSpecial
         ActiveCell.Offset(1, 0).Select
         Sheets("Submition Forms").Select
         ActiveCell.Offset(1, 0).Select
         Call FmtDetentions
         Call DetentionListAddName

      Case ""
        ActiveCell.Copy

      Case Else
          Call CorrectData
    End Select
Loop

End Sub