else without if error

[jordanburch]

Hey all,

I have the below. I just cant get it to read right. I am not sure how to stop getting the Else without if error. Please see below. Any help is appreciated!

Jordan

If ThisWorkbook.Worksheets("variables").Range("a11").Value = "October" Then

filepath3 = "K:\SHARED\TRANSFER\Enterprise Wide Suspense Initiative\Source Files\97 Field Details\" & ThisWorkbook.Worksheets("Variables").Range("A4").Value & "\" & Left(ThisWorkbook.Worksheets("Variables").Range("A9").Value, 2) & "-" & Mid(ThisWorkbook.Worksheets("Variables").Range("A9").Value, 6, 3) & Right(ThisWorkbook.Worksheets("variables").Range("A9").Value, 2) & "\Field Detail Lines\"
myfile3 = "CO097*.xlsx"

erow = wb1.Sheets("CO SAR").Cells(Rows.Count, 14).End(xlUp).Offset(1, 0).Row
    Set wb2 = Workbooks.Open(filepath & myfile)
    
   
       Dim ws As Worksheet
Dim ClearedSheet As String
ClearedSheet = ""
For Each ws In ActiveWorkbook.Worksheets
If InStr(1, ws.Name, "Detail", vbTextCompare) Then
ClearedSheet = ws.Name

            End If
        Next
        
    ShtName1 = ClearedSheet
    With wb2
     Sheets(ClearedSheet).Select
     With ActiveSheet
    If .AutoFilterMode Then
        If .FilterMode Then
            .ShowAllData
        End If
    Else
        If .FilterMode Then
            .ShowAllData
        End If
    End If
    End With
    
    Dim ShtName As String
ShtName = Sheets(ClearedSheet).Name
If Evaluate("isref('" & ShtName & "'!A1)") Then
   'sheet exists do something
Else
   'sheet doesn't exist do something else
End If
   If Evaluate("isref('" & ShtName1 & "'!A1)") Then
   wb2.Sheets(ClearedSheet).Range("q2:q25000").Value = wb2.Name
        .Sheets(ClearedSheet).Range("c2:q25000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        ElseIf Evaluate("isref('" & ShtName3 & "'!A1)") Then
        .Sheets(ClearedSheet).Range("c2:p25000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        ElseIf Evaluate("isref('" & ShtName2 & "'!A1)") Then
        .Sheets(ClearedSheet).Range("c2:p25000").Copy Destination:=wb1.Worksheets("CO SAR").Cells(erow, 1)
        .Close savechanges:=False
        
        End If
        
        
        
    End With
    
    End If
    
Else

 

[jasonb75]

Maybe it's that lone 'Else' a the very end that doesn't appear to be linked to anything?

 

[jordanburch]

Maybe it's that lone 'Else' a the very end that doesn't appear to be linked to anything?

so the first if is supposed to link to that else. I only added the If statement at the top and the else at the bottom, but it seems that its something to do with positioning. I keep trying different things but cant seem to get it right.

 

[jasonb75]

This is why you need to keep your code tidy, if you make use of indentation to keep each If aligned with the relevant Else and End If it makes it easier to follow and find such problems.

Keep all paired / grouped commands (If - Else - End If, For - Next, While - Wend, With - End With, Do - Loop, etc) aligned vertically with the code between them tabbed once to the right.

If something Then
    For this = that To other
        If whatever Then
            coffee break
        Else
            do some work
        End If
    Next
End If

The last End If in your code is actually closing the first If that you're trying to link that Else to. I have no way of knowing if that is the one that shouldn't have been closed, or if you have used another End If earlier in the code before it was needed.

 

[jordanburch]

This is why you need to keep your code tidy, if you make use of indentation to keep each If aligned with the relevant Else and End If it makes it easier to follow and find such problems.

Keep all paired / grouped commands (If - Else - End If, For - Next, While - Wend, With - End With, Do - Loop, etc) aligned vertically with the code between them tabbed once to the right.

If something Then
    For this = that To other
        If whatever Then
            coffee break
        Else
            do some work
        End If
    Next
End If

The last End If in your code is actually closing the first If that you're trying to link that Else to. I have no way of knowing if that is the one that shouldn't have been closed, or if you have used another End If earlier in the code before it was needed.

this is very helpful thank you! Finally makes sense to me lol. now on to the next problem! ha

Jordan