Collectives™ on Stack Overflow
Find centralized, trusted content and collaborate around the technologies you use most.
Learn more about Collectives
Teams
Q&A for work
Connect and share knowledge within a single location that is structured and easy to search.
Learn more about Teams
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
'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
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
With Sheets("Template")
.Copy After:=Sheets(Sheets.Count) 'Add worksheet
ActiveSheet.Name = WsName & "_" & i
End With
End If
–
–
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}
{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
'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.
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
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
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
–
–
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
