Why do I keep getting Else without If Error?

[MisterSrosis]

Dim ct As Long
Dim ct2 As Long
Dim wkb As Workbook
Dim fileN As Variant
Dim sht As Worksheet
Dim c As Range
Dim arrCnt As Long
Dim cAddressArr() As Variant
Dim conf As Variant
Dim columnBeingAnalyzed As Integer
Dim ce As Range
Dim filterS As String
Dim filterS2 As String


conf = MsgBox("Do you think you know 2 general facts about your specific component? Example: When describing a resistor we consider resistance value, what type of resistor it is (power,variable,ceramic)", vbYesNo)
arrCnt = 0


If conf = vbYes Then
    filterS = Application.InputBox("Enter category that your sepcific component belongs to(Ex: resistor,the categories a       resistor can belong to are power, variable, high voltage etc.):")
    filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")
    For ct = 0 To UBound(Results)
        fileN = "labInventory" & "\" & Results(ct)
        Set wkb = Workbooks.Open(fileN)
        Set sht = wkb.Sheets(Results(ct + 1))
        sht.Activate
        For Each c In sht.UsedRange.Cells
            If InStr(c.Value, filterS) > 0 Then
                'checking the column in which the first filterstring was found for the 2nd filterString
                columnBeingAnalyzed = c.Column
                For Each ce In sht.UsedRange.Columns(columnBeingAnalyzed)
                    If InStr(c.Value, filterS2) > 0 Then
                        arrCnt = arrCnt + 1
                        cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
                    End If
                Next ce
            End If
        Next c
    ActiveWorkbook.Close (False)
    ct = ct + 2
Else
filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")
    For ct = 0 To UBound(Results)
        arrCnt = 0
        fileN = "labInventory" & "\" & Results(ct)
        Set wkb = Workbooks.Open(fileN)
        Set sht = wkb.Sheets(Results(ct + 1))
        sht.Activate
        For Each c In sht.UsedRange.Cells
            If InStr(c.Value, filterS2) > 0 Then
                arrCnt = arrCnt + 1
                cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name
            End If
        Next c
    ActiveWorkbook.Close (False)
ct = ct + 2
End If


FilterResultsEvenFurther = cAddressArr


End Function

 

[JoeMo]

At a quick glance - looks like you are missing Next ct in two places. That will produce the error you are getting even though it has nothing to do with an If statement.

 

[MisterSrosis]

Oh that probably might be it, but in that case how do I increment the ct by 2, because by workbook name saved in the array I pass into this function is in positions 0, and all other positions corresponding to 2n, and all my sheet names are odd numbers, and the indice b4 the individual sheet names, is the workbook they correspond to. Edit: Nvm instead of waiting for a reply to a relatively easy question I think I'm just going to go with a do while loop :D

 

[JoeMo]

Oh that probably might be it, but in that case how do I increment the ct by 2, because by workbook name saved in the array I pass into this function is in positions 0, and all other positions corresponding to 2n, and all my sheet names are odd numbers, and the indice b4 the individual sheet names, is the workbook they correspond to. Edit: Nvm instead of waiting for a reply to a relatively easy question I think I'm just going to go with a do while loop :D

Delete the ct = ct +2 statements and change this:

For ct = 0 To UBound(Results)

to this:

For ct = 0 To UBound(Results) step 2

 

[DocAElstein]

..... looks like you are missing Next ct in two places. That will produce the error you are getting even though it has nothing to do with an If statement.

This sort of error came up a lot with me and as a beginner I was frequently trying to find the missing bit as stated in the error message.

.....The problem is the VB does not examine each structure individually, rather it treats them all like they were the same and simply counts the opening and closing statements for all blocks... when they do not balance, VB simply assumes the last block structure is at fault....

in http://www.mrexcel.com/forum/excel-questions/791006-next-without-help-please.html. The code from MisterSrosis is good indented so the missing nexts show up quite easilly

<font face=Calibri><SPAN style="color:#00007F">Function</SPAN> test()<br><SPAN style="color:#00007F">Dim</SPAN> ct <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ct2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> wkb <SPAN style="color:#00007F">As</SPAN> Workbook<br><SPAN style="color:#00007F">Dim</SPAN> fileN <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> sht <SPAN style="color:#00007F">As</SPAN> Worksheet<br><SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> arrCnt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> cAddressArr() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> conf <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> columnBeingAnalyzed <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ce <SPAN style="color:#00007F">As</SPAN> Range<br><SPAN style="color:#00007F">Dim</SPAN> filterS <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> filterS2 <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br><br>conf = MsgBox("Do you think you know 2 general facts about your specific component? Example: When describing a resistor we consider resistance value, what type of resistor it is (power,variable,ceramic)", vbYesNo)<br>arrCnt = 0<br><br><br><SPAN style="color:#00007F">If</SPAN> conf = vbYes <SPAN style="color:#00007F">Then</SPAN><br>    filterS = Application.InputBox("Enter category that your sepcific component belongs to(Ex: resistor,the categories a       resistor can belong to are power, variable, high voltage etc.):")<br>    filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")<br>    <SPAN style="color:#00007F">For</SPAN> ct = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Results)<br>        fileN = "labInventory" & "\" & Results(ct)<br>        <SPAN style="color:#00007F">Set</SPAN> wkb = Workbooks.Open(fileN)<br>        <SPAN style="color:#00007F">Set</SPAN> sht = wkb.Sheets(Results(ct + 1))<br>        sht.Activate<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> sht.UsedRange.Cells<br>            <SPAN style="color:#00007F">If</SPAN> InStr(c.Value, filterS) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                <SPAN style="color:#007F00">'checking the column in which the first filterstring was found for the 2nd filterString</SPAN><br>                columnBeingAnalyzed = c.Column<br>                <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ce <SPAN style="color:#00007F">In</SPAN> sht.UsedRange.Columns(columnBeingAnalyzed)<br>                    <SPAN style="color:#00007F">If</SPAN> InStr(c.Value, filterS2) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                        arrCnt = arrCnt + 1<br>                        cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name<br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>                <SPAN style="color:#00007F">Next</SPAN> ce<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    ActiveWorkbook.Close (False)<br>    ct = ct + 2<br>    <SPAN style="color:#00007F">Next</SPAN> ct <SPAN style="color:#007F00">'#########################################</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br>filterS2 = Application.InputBox("Enter any further Identification/value/ID number of Component")<br>    <SPAN style="color:#00007F">For</SPAN> ct = 0 <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(Results)<br>        arrCnt = 0<br>        fileN = "labInventory" & "\" & Results(ct)<br>        <SPAN style="color:#00007F">Set</SPAN> wkb = Workbooks.Open(fileN)<br>        <SPAN style="color:#00007F">Set</SPAN> sht = wkb.Sheets(Results(ct + 1))<br>        sht.Activate<br>        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> sht.UsedRange.Cells<br>            <SPAN style="color:#00007F">If</SPAN> InStr(c.Value, filterS2) > 0 <SPAN style="color:#00007F">Then</SPAN><br>                arrCnt = arrCnt + 1<br>                cAddressArr(arrCnt) = c.Address + "" + sht.Name + "" + wkb.Name<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>        <SPAN style="color:#00007F">Next</SPAN> c<br>    <SPAN style="color:#00007F">Next</SPAN> ct <SPAN style="color:#007F00">' ########################################</SPAN><br>    ActiveWorkbook.Close (False)<br>ct = ct + 2<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><br>FilterResultsEvenFurther = cAddressArr<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN></FONT>

As discussed in http://www.mrexcel.com/forum/excel-questions/791006-next-without-help-please.html writing things like For and Next in pairs usually helps keep the problem at bay.
Good Tip I found as a beginner.
Alan.