2 different errors im struggling with

Hi everyone,

I'm very new to all this and have been working on a project to help out with work. I've been following some online videos and tried some different stuff I've found on these forums, but still am at a loss on how to fix the errors.

The first one was a 'block if without end if', but following some replies i saw on different threads i added a extra 'end if' and now its become a 'for without next' error. That is for a user-form error messagebox I'm trying to have. When someone doesn't fill in all the fields, i want the message to play and the data to not be added to the spreadsheet until this is complete.

The second is 'Object variable or with variable not set'. That is for a macro i recorded basically using a combo box to select different options to filter the data (i know i could basically just use a filter, but i think this would be more user friendly).

The whole project has become exceedingly messy. I've got a lot of random code lines in there that i have no idea where they come from.

I've attached a copy of my project.

Thanks for any help people can provide. I'm sorry if questions are a bit stupid.

Regards,

Originally Posted by Richard Buttrey

Hi and welcome to the forum

At the moment you have two IF statements and only one End If. They should be paired.

You also have a For Each Ctrl without a Next Ctrl

Thank you both for your input and really quick replies!

Thanks Richard, I'll have a look at some YouTube videos about that. Random question is there anyone online you who's videos you recommend? I feel like I'm not learning the important basics, with the current videos I'm watching.

Originally Posted by Norie

This should sort the first error.

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")
    ws.Cells(newRow, 5).Value = Date

    Clear_Form

    'validate textbox entry
    Dim ctrl As Control
    For Each ctrl In Me.Controls
        If TypeOf ctrl Is MSForms.TextBox Then
            If ctrl.Value = vbNullString Then
                MsgBox "You must complete all entries"
                ctrl.SetFocus
                Unload Me
              Exit Sub
            End If
        End If
    Next ctrl
    
End Sub

Not sure where to look for the other code that's causing problems, where is that located?

I added that code Norie thank you very much. The problem I'm still having is even though i don't complete the userform, it still automatically adds the IST 'no' section. How do i rectify this? The second error is located in module 2, no idea how I've ended up putting it there lol.

Originally Posted by Parth007

Please run the attached file...
Made it working without errors

Thanks Parth007 for that. I'm still getting a error with the combobox on the spreadsheet that's supposed to sort the data? P.S i like your choice in beers

Thanks for your help guys! This is going to help me no end at work. I truly appreciate you giving up your time to help.

Norie I moved the module into the correct project (at least i think i have lol) Overstocks fixed by Parth and Norie.xlsm

Otherwise heres the code

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 again guyss, I hope i can contribute back to the forum one day

I thought maybe i was getting the 2nd error because i was using a normal combo box. I've tried it with a activeX control and also without Option Explicit. All to no avail. Still getting the same error