VBA Next without for complie error

[Mr_Smit]

Hi there everyone,

I've recently been trying to learn some basic vba to help with duties at my work. I've been following some youtube videos and came into a problem i cant seem to find the answer for. I keep getting a 'next without for' compile error. I know its because i was following youtube videos that weren't using option explicit and i am. So i think it has to do with not defining the variables properly. I've attempted a few different things that i've seen suggested on here, but its still giving me trouble.

here is my code;

Option Explicit
Private Sub btnAdd_Click()
    
    Dim ws As Worksheet
    Set ws = Worksheets("Overstocks")
    
    Dim i As Variant
    
    Dim newRow As Long
    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1
    
    ws.Cells(newRow, 1).Value = Me.txtBrand.Value
    ws.Cells(newRow, 2).Value = Me.cbCategory.Value
    ws.Cells(newRow, 3).Value = Me.txtQuantity.Value
    
        If opYes.Value = True Then
        ws.Cells(newRow, 4).Value = "Yes"
        Else:
        ws.Cells(newRow, 4).Value = "No"
    End If
    
    Next i

Clear_Form

End Sub

Sub Clear_Form()

For Each Control In Me.Controls
    
    Select Case TypeName(ctrl)
        
        Case "TextBox"
            ctrl.Text = ""
            
        Case "ComboBox"
            ctrl.ListIndex = -1
        Case "OptionButton"
            ctrl.Value = False
                End If
                Next i
        
        End Select


Next

End Sub
Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub UserForm_Initialize()
    With cbCategory
        .AddItem "Beer"
        .AddItem "Ready To Drink"
        .AddItem "Spirits"
        .AddItem "Wine - Red"
        .AddItem "Wine - Sparkling"
        .AddItem "Wine - White"
    
    End With
    
End Sub

Thanks very much for any help/suggestions people can provide.

 

[KeepTrying]

Hi,

Where is "for i" in first section of your code? I see only "next i" at the end of it.

 

[MARK858]

...and how are you supposed to be using newRow as currently it looks like you might be using it in the loop when it is defined as the last cell in column A +1 row?

... and what is opYes supposed to be as you haven't defined it?

 

[dmt32]

Hi
As error states, you have a NEXT without a FOR in both btnAdd_Click & an additional Next in the Clear_Form code but in each case, cannot see any need for these.

Try this update to your code & see if helps:

Option Explicit
Private Sub btnAdd_Click()


    Dim ws As Worksheet
    Dim i As Variant
    Dim newRow As Long


    Set ws = Worksheets("Overstocks")


    newRow = Application.WorksheetFunction.CountA(ws.Range("A:A")) + 1


    ws.Cells(newRow, 1).Value = Me.txtBrand.Value
    ws.Cells(newRow, 2).Value = Me.cbCategory.Value
    ws.Cells(newRow, 3).Value = Me.txtQuantity.Value
    ws.Cells(newRow, 4).Value = IIf(opYes.Value, "Yes", "No")
    
    Clear_Form
End Sub


Sub Clear_Form()
Dim Ctrl As Control
    For Each Ctrl In Me.Controls
        Select Case TypeName(Ctrl)
        Case "TextBox"
            Ctrl.Text = ""
        Case "ComboBox"
            Ctrl.ListIndex = -1
        Case "OptionButton"
            Ctrl.Value = False
        End Select
    Next Ctrl
End Sub
Private Sub btnClose_Click()
Unload Me
End Sub


Private Sub UserForm_Initialize()
    With cbCategory
        .AddItem "Beer"
        .AddItem "Ready To Drink"
        .AddItem "Spirits"
        .AddItem "Wine - Red"
        .AddItem "Wine - Sparkling"
        .AddItem "Wine - White"
    End With
End Sub

Hope Helpful

Dave

 

[Mr_Smit]

Hi,

Where is "for i" in first section of your code? I see only "next i" at the end of it.

...and how are you supposed to be using newRow as currently it looks like you might be using it in the loop when it is defined as the last cell in column A +1 row?

... and what is opYes supposed to be as you haven't defined it?

This is embarrassing but since i dont even understand the questions i can see i've a)attempted something out of my current skill level and b) Haven't actually UNDERSTOOD the videos i was following. Rather i was just copying. I'll go back and try review. Thanks guys.

Hi
As error states, you have a NEXT without a FOR in both btnAdd_Click & an additional Next in the Clear_Form code but in each case, cannot see any need for these.

Try this update to your code & see if helps:


Dave

You're a legend Dave! That fixed the problem. I'll study how you fixed it and try to learn. Thanks Dave

 

[Mr_Smit]

I've come across another problem that's driving me a bit nuts. I'm getting 'object variable or with block variable not set runtime error 91' when i try to run my macro. I recorded it so i could use a list box to sort through my data.

Option Explicit

Sub Brand2()
'
' Brand2 Macro
' Sort A-Z for brand

'
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Add Key:= _
        Range("A1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub Category()
'
' Category Macro
' Sort Category a-z
'

'
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Add Key:= _
        Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub Quantity()
'
' Quantity Macro
' Sort Quantity Highest to Lowest
'

'
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Add Key:= _
        Range("C1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub IST()
'
' IST Macro
' Sort Z-A
'

'
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Add Key:= _
        Range("D1"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub
Sub Date1()
'
' Date1 Macro
' Lowest First
'

'
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort.SortFields.Add Key:= _
        Range("E1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Overstocks").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
End Sub

Thanks for any help people can provide. I wasnt sure if i should start a new thread or try to keep all my newb questions in one place