# If ElseIf and Else statements to call several subs



## markswjh (Tuesday at 11:32 AM)

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 (Tuesday at 12:57 PM)

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 (Tuesday at 3:18 PM)

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 (Tuesday at 3:27 PM)

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 (Wednesday at 11:01 AM)

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 (Wednesday at 5:21 PM)

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 (Wednesday at 10:31 PM)

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 (Thursday at 12:30 AM)

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 (Thursday at 10:30 AM)

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 (Thursday at 11:36 AM)

> 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
```


----------



## markswjh (Tuesday at 11:32 AM)

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
```


----------



## markswjh (Thursday at 11:36 AM)

rather oddly having the loop not work properly previously it is now exiting when it should. I think it may have been the machine rather than the code that was causing the issue. Sorry!

It is now exiting when it is blank from the first set of code you sent which it wasn't previously, and it now runs the call CorrectData when it should. I have no idea why


----------

