2

This is my first vba project so please be patient. I have created an Excel Userform to add info to a database. When I click "add" I have vba script that should find the first open worksheet for that year (ex. Name_2018_1, Name_2018_2, Name_2018_3) or create a new consecutively named worksheet.

I keep receiving Compile Errors based on where I place my "then" and "exit for" statements.

I want the counter to either find the first worksheet with an available row or loop all the way through i and then make a new sheet based on the i value.

How do I correctly set up a for loop after my else? If anyone could point out what I am doing wrong it would be appreciated.

'Dim myFirstBlankRow As Long
'Dim i               As Long
'Dim WsName          As String
'Dim WsNamei         As String
'Dim ws              As Worksheet
'Dim counter         As Integer

'Declare worksheet names
WsName = "Name_" & Year(Me.DTPicker1.value) & "*" 
WsNamei = "Name_" & Year(Me.DTPicker1.value) & "_" & i

For Each ws In Sheets
    If ws.Name = WsName Or ws.Name Like WsName & "*" Then i = i + 1
Next

'If no worksheet exists, then make a new one, else loop through until an empty row is found

If i = 0 Then 'No worksheet with the year selected by DTPicker
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add new worksheet
        ActiveSheet.Name = WsName & "_1"
    End With

*If I place the "Then Exit For" on one line I receive a Compile Error "Else without If"

Else:
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) = True Then Exit For
    Next counter


Else 'No recognizing the original If statement
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        ActiveSheet.Name = WsName & "_" & i
    End With
End If

*However, when I place the "Then" & "Exit For" on separate lines I receive a Compile Error"Next without For"

Else:
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) = True Then
        Exit For
    Next counter

Else
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        ActiveSheet.Name = WsName & "_" & i
    End With
End If

'Added for my modifications I want the code to (1st) check for an original worksheet and make one if there is not. (2nd) If that original worksheet exists but is full, I want to make sure there is not already a new worksheet. If there is it becomes the active sheet. (3rd) if there is not another available worksheet I need the script to make one.

I was originally having issues with a new worksheet being created and only one entry being applied to that worksheet prior to another worksheet being created, hence the 3 steps.

Should I split off the first If and then have the last 2 as their own entity for if the first worksheet is full? 

If i = 0 Then 'No worksheet with the year selected by DTPicker
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        i = 1
        ActiveSheet.Name = WsName & "_" & i
    End With
ElseIf
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) = True Then
        Exit For
        End If
    Next counter
Else
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        ActiveSheet.Name = WsName & "_" & i
    End With
End If
Improve this question
3
  • 1
    That Else: should be ElseIf - you have 2 Elses in your If statement. Commented Dec 20, 2018 at 21:03
  • You can't do If / Else / Else / Else / ... / End If. You need If / ElseIf / ElseIf / End If. So for your final Else, when would that trigger? If i <> 0? or is that the second Else, and you want to fire that final one regardless what i is? Commented Dec 20, 2018 at 21:03
  • 2
    This seems like a perfect opportunity to use Select Case instead of If when there are multiple conditions. Commented Dec 20, 2018 at 21:42

2 Answers 2

Reset to default
3

If Then isn't followed by a statement, say, like this:

If IsEmpty(Range("A1048576").value) Then Exit For

Then it's the block syntax and since it's a block, it needs to be closed, in this case with End If:

If IsEmpty(Range("A1048576").value) Then
    Exit For
End If

That's why you get "next without for", because then without the End If, the compiler encounters Next before it gets to any End If, so the For counter loop block is considered not terminated.

Next problem, you can't have an If...Else...End If block with more than 1 Else.

Else:
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) = True Then Exit For
    Next counter


Else 'No recognizing the original If statement

VBA can't parse this; I can't parse this either. I've no idea what you intended to do here. If i is 0 you execute something, otherwise you execute something else - all bases are covered, an additional "else" just doesn't fit anywhere.

This would a template to work with:

If {bool-expression} Then
    {statements}
ElseIf {bool-expression} Then
    {statements}
Else
    {statements}
End If

The Else token identifies the "fallback" case. Having two makes no sense. Note that the instruction separator token : is redundant in Else:, and makes it look like a line label: avoid that.

How to avoid this

When you type an If statement, type the corresponding End If immediately:

If something Then
    'type here
End If

When you later nest things, keep immediately closing whatever blocks you open:

If something Then
    For i = 0 To 10
        'type here now
    Next
Else
    'get back here later
End If

If things start being too hard to follow, grab the entire block and pull it into its own private procedure, passing whatever parameters are needed.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

Next without For translates to missing End If

In the first case the Else: should be replaced with Else and the second Else should be removed.

In the second case you additionally are missing an End If, regardless of the Error. The End If should be below the Exit For (above Next Counter).

They are equivalent, you are just using two different ways of the If statement.

Case 1

If i = 0 Then 'No worksheet with the year selected by DTPicker
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add new worksheet
        ActiveSheet.Name = WsName & "_1"
    End With
  Else
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) Then Exit For
    Next counter
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        ActiveSheet.Name = WsName & "_" & i
    End With
End If

Case 2

If i = 0 Then 'No worksheet with the year selected by DTPicker
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add new worksheet
        ActiveSheet.Name = WsName & "_1"
    End With
  Else
    For counter = 1 To i Step 1
        Worksheets(WsName & "_" & counter).Activate
        If IsEmpty(Range("A1048576").value) Then
            Exit For
        End If
    Next counter
    With Sheets("Template")
        .Copy After:=Sheets(Sheets.Count) 'Add worksheet
        ActiveSheet.Name = WsName & "_" & i
    End With
End If
Improve this answer

4 Comments

ElseIf with what condition though?
@Mathieu Guindon: Have you thought about what the code even does? This counting is pretty suspicious. Shouldn't the last 'i' be 'counter'?
TBH I haven't looked in any level of details, just the control flow outline - nice edit there, +1
@Mathieu Guindon: Me too until now. Nice 'If' lecture.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.