When I go through the code I have an "End If" that vba tells me does not have a corresponding "If" block with it. If you cut and paste the code into Notepad++, the error happens on line 638. Below the code I will include a summarized version for ease of seeing the "conditional statements". I know there are probably better and more efficient ways to do this, but I am looking for the issue with this particular code and would like to avoid having to do a full re-write if possible. Any help to figure out where the issue lies is greatly appreciated.
Here is the code:
Public Sub increments()
'check
Dim main As Worksheet: Set main = ThisWorkbook.Worksheets("main")
Dim pc As Worksheet: Set pc = ThisWorkbook.Worksheets("Programs_Courses")
Dim pso As Worksheet: Set pso = ThisWorkbook.Worksheets("ps_output")
lastrow = main.Range("A" & main.Rows.Count).End(xlUp).Row
Dim DestRow As Integer
'programs variable declarations
Dim acc_con As Integer, _
bunch of others...
Dim gen_manag_con As Integer, _
bunch of others...
'initialize program variables to 0
acc_con = 0
all other variables also set to 0
DestRow = 2
For i = 2 To lastrow
If main.Cells(i, 1) = main.Cells(i + 1, 1) Then
'Accounting 1
If main.Cells(i, 7) = pc.Cells(3, 3) Then
acc_con = acc_con + 1
more increments
'Accounting 2
ElseIf main.Cells(i, 7) = pc.Cells(4, 3) Then
ton more incrementing but had to cut due to length
End If
'Check for concentrator status
If acc_con = 2 Or _
pl_an_sys_con = 50 Or _
auto_tech_con = 2 Or _
cul_art_con = 2 Or _
dig_art_des_con = 2 Or _
ece_con = 2 Or _
envi_nat_res_con = 2 Or _
gen_manag_con = 2 Or _
graph_comm_con = 2 Or _
health_sci_con = 2 Or _
horti_con = 1 Or _
mark_manag_con = 2 Or _
media_tech_con = 2 Or _
pltw_biomed_sci_con = 2 Or _
pltw_pre_eng_con = 2 Or _
pltw_pre_eng_con = 3 Or _
sports_med_con = 2 Or _
fam_con_sci_con = 2 Or _
prog_soft_dev_con = 2 Then
pc.Activate
term_match = Application.Match(main.Cells(i, 14), pc.Range(Cells(4, 77), Cells(30, 77)), 0)
If IsError(term_match) Then
ElseIf term_match > 0 Then
If IsEmpty(pso.Cells(DestRow, 2)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 2) = "Y"
pso.Cells(DestRow, 6) = pc.Cells(term_match, 78)
ElseIf Not IsEmpty(pso.Cells(DestRow, 2)) And IsEmpty(pso.Cells(DestRow, 8)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 8) = "Y"
pso.Cells(DestRow, 12) = pc.Cells(term_match, 78)
End If
End If
'reset concentrator
If acc_con = 2 Then acc_con = 0
ElseIf pl_an_sys_con = 50 Then pl_an_sys_con = 0
ElseIf auto_tech_con = 2 Then auto_tech_con = 0
ElseIf cul_art_con = 2 Then cul_art_con = 0
ElseIf dig_art_des_con = 2 Then dig_art_des_con = 0
ElseIf ece_con = 2 Then ece_con = 0
ElseIf envi_nat_res_con = 2 Then envi_nat_res_con = 0
ElseIf gen_manag_con = 2 Then gen_manag_con = 0
ElseIf graph_comm_con = 2 Then graph_comm_con = 0
ElseIf health_sci_con = 2 Then health_sci_con = 0
ElseIf horti_con = 1 Then horti_con = 0
ElseIf mark_manag_con = 2 Then mark_manag_con = 0
ElseIf media_tech_con = 2 Then media_tech_con = 0
ElseIf pltw_biomed_sci_con = 2 Then pltw_biomed_sci_con = 0
ElseIf pltw_pre_eng_con = 2 Then pltw_pre_eng_con = 0
ElseIf pltw_pre_eng_con = 3 Then pltw_pre_eng_con = 0
ElseIf sports_med_con = 2 Then sports_med_con = 0
ElseIf fam_con_sci_con = 2 Then fam_con_sci_con = 0
ElseIf prog_soft_dev_con = 2 Then prog_soft_dev_con = 0
End If
'Check for completer status
ElseIf acc_comp >= 21 And acc_comp <= 25 Or _
pl_an_sys_comp = 50 Or _
pl_an_sys_comp = 60 Or _
auto_tech_comp >= 21 And auto_tech_comp <= 23 Or _
cul_art_comp >= 21 And cul_art_comp <= 25 Or _
dig_art_des_comp >= 21 And dig_art_des_comp <= 23 Or _
ece_comp >= 31 And ece_comp <= 37 Or _
envi_nat_res_comp = 40 Or _
envi_nat_res_comp = 50 Or _
gen_manag_comp >= 21 And gen_manag_comp <= 26 Or _
graph_comm_comp >= 21 Or graph_comm_comp <= 23 Or _
health_sci_comp >= 21 Or health_sci_comp <= 28 Or _
horti_comp = 40 Or _
horti_comp = 50 Or _
mark_manag_comp >= 21 And mark_manag_comp <= 24 Or _
mark_manag_comp = 30 Or _
media_tech_comp >= 21 And media_tech_comp <= 23 Or _
pltw_biomed_sci_comp >= 21 And pltw_biomed_sci_comp <= 26 Or _
pltw_pre_eng_comp >= 22 And pltw_pre_eng_comp <= 26 Or _
sports_med_comp >= 21 And sports_med_comp <= 27 Or _
fam_con_sci_comp >= 21 And fam_con_sci_comp <= 26 Or _
fam_con_sci_comp = 30 Or _
prog_soft_dev_comp >= 21 And prog_soft_dev_comp <= 25 Then
pc.Activate
term_match = Application.Match(main.Cells(i, 14), pc.Range(Cells(4, 77), Cells(30, 77)), 0)
If IsError(term_match) Then
ElseIf term_match > 0 Then
If IsEmpty(pso.Cells(DestRow, 3)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 3) = "Y"
pso.Cells(DestRow, 5) = pc.Cells(term_match, 78)
ElseIf Not IsEmpty(pso.Cells(DestRow, 3)) And IsEmpty(pso.Cells(DestRow, 9)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 9) = "Y"
pso.Cells(DestRow, 11) = pc.Cells(term_match, 78)
End If
End If
'reset completer
If acc_comp >= 21 And acc_comp <= 25 Then acc_comp = 0
ElseIf pl_an_sys_comp = 50 Or _
pl_an_sys_comp = 60 Then pl_an_sys_comp = 0
ElseIf auto_tech_comp >= 21 And auto_tech_comp <= 23 Then auto_tech_comp = 0
ElseIf cul_art_comp >= 21 And cul_art_comp <= 25 Then cul_art_comp = 0
ElseIf dig_art_des_comp >= 21 And dig_art_des_comp <= 23 Then dig_art_des_comp = 0
ElseIf ece_comp >= 31 And ece_comp <= 37 Then ece_comp = 0
ElseIf envi_nat_res_comp = 40 Or _
envi_nat_res_comp = 50 Then envi_nat_res_comp = 0
ElseIf gen_manag_comp >= 21 And gen_manag_comp <= 26 Then gen_manag_comp = 0
ElseIf graph_comm_comp >= 21 Or graph_comm_comp <= 23 Then graph_comm_comp = 0
ElseIf health_sci_comp >= 21 Or health_sci_comp <= 28 Then health_sci_comp = 0
ElseIf horti_comp = 40 Or _
horti_comp = 50 Then horti_comp = 0
ElseIf mark_manag_comp >= 21 And mark_manag_comp <= 24 Or _
mark_manag_comp = 30 Then mark_manag_comp = 0
ElseIf media_tech_comp >= 21 And media_tech_comp <= 23 Then media_tech_comp = 0
ElseIf pltw_biomed_sci_comp >= 21 And pltw_biomed_sci_comp <= 26 Then pltw_biomed_sci_comp = 0
ElseIf pltw_pre_eng_comp >= 22 And pltw_pre_eng_comp <= 26 Then pltw_pre_eng_comp = 0
ElseIf sports_med_comp >= 21 And sports_med_comp <= 27 Then sports_med_comp = 0
ElseIf fam_con_sci_comp >= 21 And fam_con_sci_comp <= 26 Or _
fam_con_sci_comp = 30 Then fam_con_sci_comp = 0
ElseIf prog_soft_dev_comp >= 21 And prog_soft_dev_comp <= 25 Then prog_soft_dev_comp = 0
End If
End If
Else
'Accounting 1
If main.Cells(i, 7) = pc.Cells(3, 3) Then
acc_con = acc_con + 1
more increments
'Accounting 2
ElseIf main.Cells(i, 7) = pc.Cells(4, 3) Then
ton more incrementing but had to cut due to length
End If
'Check for concentrator status
If acc_con = 2 Or _
pl_an_sys_con = 50 Or _
auto_tech_con = 2 Or _
cul_art_con = 2 Or _
dig_art_des_con = 2 Or _
ece_con = 2 Or _
envi_nat_res_con = 2 Or _
gen_manag_con = 2 Or _
graph_comm_con = 2 Or _
health_sci_con = 2 Or _
horti_con = 1 Or _
mark_manag_con = 2 Or _
media_tech_con = 2 Or _
pltw_biomed_sci_con = 2 Or _
pltw_pre_eng_con = 2 Or _
pltw_pre_eng_con = 3 Or _
sports_med_con = 2 Or _
fam_con_sci_con = 2 Or _
prog_soft_dev_con = 2 Then
pc.Activate
term_match = Application.Match(main.Cells(i, 14), pc.Range(Cells(4, 77), Cells(30, 77)), 0)
If IsError(term_match) Then
ElseIf term_match > 0 Then
If IsEmpty(pso.Cells(DestRow, 2)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 2) = "Y"
pso.Cells(DestRow, 6) = pc.Cells(term_match, 78)
ElseIf Not IsEmpty(pso.Cells(DestRow, 2)) And IsEmpty(pso.Cells(DestRow, 8)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 8) = "Y"
pso.Cells(DestRow, 12) = pc.Cells(term_match, 78)
Else
End If
End If
'Check for completer status
ElseIf acc_comp >= 21 And acc_comp <= 25 Or _
pl_an_sys_comp = 50 Or _
pl_an_sys_comp = 60 Or _
auto_tech_comp >= 21 And auto_tech_comp <= 23 Or _
cul_art_comp >= 21 And cul_art_comp <= 25 Or _
dig_art_des_comp >= 21 And dig_art_des_comp <= 23 Or _
ece_comp >= 31 And ece_comp <= 37 Or _
envi_nat_res_comp = 40 Or _
envi_nat_res_comp = 50 Or _
gen_manag_comp >= 21 And gen_manag_comp <= 26 Or _
graph_comm_comp >= 21 Or graph_comm_comp <= 23 Or _
health_sci_comp >= 21 Or health_sci_comp <= 28 Or _
horti_comp = 40 Or _
horti_comp = 50 Or _
mark_manag_comp >= 21 And mark_manag_comp <= 24 Or _
mark_manag_comp = 30 Or _
media_tech_comp >= 21 And media_tech_comp <= 23 Or _
pltw_biomed_sci_comp >= 21 And pltw_biomed_sci_comp <= 26 Or _
pltw_pre_eng_comp >= 22 And pltw_pre_eng_comp <= 26 Or _
sports_med_comp >= 21 And sports_med_comp <= 27 Or _
fam_con_sci_comp >= 21 And fam_con_sci_comp <= 26 Or _
fam_con_sci_comp = 30 Or _
prog_soft_dev_comp >= 21 And prog_soft_dev_comp <= 25 Then
pc.Activate
term_match = Application.Match(main.Cells(i, 14), pc.Range(Cells(4, 77), Cells(30, 77)), 0)
If IsError(term_match) Then
ElseIf term_match > 0 Then
If IsEmpty(pso.Cells(DestRow, 3)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 3) = "Y"
pso.Cells(DestRow, 5) = pc.Cells(term_match, 78)
ElseIf Not IsEmpty(pso.Cells(DestRow, 3)) And IsEmpty(pso.Cells(DestRow, 9)) Then
pso.Cells(DestRow, 1) = main.Cells(i, 1)
pso.Cells(DestRow, 9) = "Y"
pso.Cells(DestRow, 11) = pc.Cells(term_match, 78)
Else
End If
End If
End If
'reset all concentrator and completer counts (next line is a new student)
acc_con = 0
acc_comp = 0
pl_an_sys_con = 0
pl_an_sys_comp = 0
auto_tech_con = 0
auto_tech_comp = 0
cul_art_con = 0
cul_art_comp = 0
dig_art_des_con = 0
dig_art_des_comp = 0
ece_con = 0
ece_comp = 0
envi_nat_res_con = 0
envi_nat_res_comp = 0
gen_manag_con = 0
gen_manag_comp = 0
graph_comm_con = 0
graph_comm_comp = 0
health_sci_con = 0
health_sci_comp = 0
horti_con = 0
horti_comp = 0
mark_manag_con = 0
mark_manag_comp = 0
media_tech_con = 0
media_tech_comp = 0
pltw_biomed_sci_con = 0
pltw_biomed_sci_comp = 0
pltw_pre_eng_con = 0
pltw_pre_eng_comp = 0
sport_med_con = 0
sport_med_comp = 0
fam_con_sci_con = 0
fam_con_sci_comp = 0
prog_soft_dev_con = 0
prog_soft_dev_comp = 0
DestRow = pso.Range("A" & main.Rows.Count).End(xlUp).Offset(1, 0).Row
End If
Next i
End Sub
Here is the summary
Public Sub increments()
for i = 2 to lastrow
if stu_id = stu_id + 1 then
if .....
increment variables
end if
if ..... (check con status)
if .....
if .....
display results
end if
end if
if .....
reset con variables
end if
elseif ..... (check comp status)
if .....
if .....
display results
end if
end if
if .....
reset comp variables
end if
end if 'line the error occurs
else
if .....
increment variables
end if
if ..... (check con status)
if .....
if .....
display results
end if
end if
elseif ..... (check comp status)
if .....
if .....
display results
end if
end if
end if
reset all variables to 0
end if
next i
end sub
Thank you in advance. Sincerely, a forever student.
You have two places where you mix up single line If-statements with normal If-statements.
Bigben found already 1 of the 2 places (see comments), the other is where you have a comment in your code 'reset concentrator
You can see if an If
-statement is a Single Line If when you have code after the Then
keyword:
If acc_con = 2 Then acc_con = 0
That statement is short for
If acc_con = 2 Then
acc_con = 0
End If
As you notice, there is an implicit End If
- with other words, you can't have an Else
or ElseIf
for that If on a new line. You can have an Else
(but no ElseIf
) on the same line (but you should avoid that).
If acc_con = 2 Then acc_con = 0 Else acc_con = 99
So change your code to
'reset concentrator
If acc_con = 2 Then
acc_con = 0
ElseIf pl_an_sys_con = 50 Then
pl_an_sys_con = 0
ElseIf auto_tech_con = 2 Then
auto_tech_con = 0
ElseIf cul_art_con = 2 Then
(...)
and
'reset completer
If acc_comp >= 21 And acc_comp <= 25 Then
acc_comp = 0
ElseIf pl_an_sys_comp = 50 Or pl_an_sys_comp = 60 Then
pl_an_sys_comp = 0
ElseIf auto_tech_comp >= 21 And auto_tech_comp <= 23 Then
auto_tech_comp = 0
(...)