Methods to deal with compile error: else without if

[Prevost]

Hi There,

I am having trouble with finding what is actually wrong with my code in regards to a specific ELSE condition. It highlights the ELSE that is apparently not related to an IF statement. Unfortunately, I am the only one here that has a basic (maybe even very basic because I can't get this to work!) understanding of VBA for excel so I can't get a fresh pair of eyes to analyze this. Does anyone have any suggestions or know of any ways to effectively deal with ELSE without IF compile errors, or more generally compile errors? I have posted the code below as reference for what I am having trouble with and put a comment above the problematic ELSE statement.

Thanks in advance for any help/advice (and ps, I am not 100% sure that all the code will work as desired, but I cannot solve the compile error to determine that)

Option Explicit
Option Base 1
Sub SelectRangesCalculateCMpractice()
        
    Dim strandrange As Range, gaugerange As Range, cmrange As Range, strandcell As Range, gaugecell As Range, gaugetest As Range, selectcell As Range
    Dim strandrow As Long, strandcolumn As Long, gaugerow As Long, gaugecolumn As Long, refdiameter As Long
    Dim strandarray As Variant, gaugearray As Variant, cmarray As Variant
    Dim i As Long, j As Long, k As Long
    Dim AWG(1 To 55) As Long, AWGCell(1 To 55) As Long                  'these are declared in my actual code but I don't think the values are important here
    Dim test As Long

        gaugetest = 0
        refdiameter = 0.001

        'Have user select range of cells->check these cells to ensure only 1 column & only numbers in cells
        Set strandrange = Application.InputBox(Prompt:="Please Select Strand Range", Title:="Strand Range Select", Type:=8)
        If strandrange.Columns.Count > 1 Then
            MsgBox ("Invalid Number of Strand Columns")
        Else
            For Each strandcell In strandrange
                If IsNumeric(strandcell) = False Then
                    MsgBox ("Strand Range Contains Non-Numbers")
                    Exit Sub
                    End
                Else
                End If
            Next
            
            'Have user select range of cells->check these cells for 1 column and only numbers in cells
            Set gaugerange = Application.InputBox(Prompt:="Please Select Gauge Range", Title:="Gauge Range Select", Type:=8)
                If gaugerange.Columns.Count > 1 Then
                    MsgBox ("Invalid Number of Gauge Columns")
                    Exit Sub
                ElseIf gaugerange.Rows.Count <> strandrange.Rows.Count Then
                    MsgBox ("Selected Strand and Gauge Ranges Are Not The Same Size")
                Else
                    
                    'Check each cell value to ensure it matches one of the declared variables
                    For Each gaugecell In gaugerange
                        If IsNumeric(gaugecell) = True Then
                            For i = 1 To 55
                                If gaugecell = AWGCell(i) Then
                                    gaugetest = 1
                                    Exit For
                                Else
                                End If
                            If gaugetest = 0 Then
                                MsgBox ("There is an invalid gauge value")
                                Exit Sub
                            Else
                            End If
                            
                        'this is the else that causes the error
                        Else
                            MsgBox ("Selected Gauge Range Contains Non-Number Value")
                        End If
                    Next gaugecell
                End If
                
                'convert ranges to arrays to perform calculations
                strandarray = strandrange.Value
                gaugearray = gaugerange.Value
                Set selectcell = Application.InputBox("Select Cell For Data", Type:=8)
                    For i = 1 To i = UBound(gaugearray, 1)
                        For j = 1 To 55
                            If gaugearray(i, 1) = AWGCell(j) Then
                                cmarray(i, 1) = strands * (AWG(j) ^ 2 / refdiameter ^ 2)
                                Exit For
                            Else
                            End If
                        Next j
                    Next i
                
                'display new array on worksheet
                For k = 1 To UBound(cmarray, 1)
                    ActiveCell.Offset(k - 1, 0) = cmarray(k, 1)
                Next k
        End If
End Sub

 

[John_w]

Because the For i loop is missing its Next:

                            For i = 1 To 55
                                If gaugecell = AWGCell(i) Then
                                    gaugetest = 1
                                    Exit For
                                Else
                                End If
                            Next

I've no idea if that is the correct place for the Next, but it looks correct. Also, you don't need the Else statements if there is nothing in the Else clause.

 

[Prevost]

Thanks. That fixed it. And thanks for the redundant ELSE tip. Much appreciated!