Edited code now i get End With Without With error

[ipbr21054]

Evening,

I have the code below

Ive added the line of code shown in red so the year function will only be added if combobo3 was selected for HONDA
Im now getting the error message End With Without With and the text shown in blue below is highlighted in yellow when i debug.

I dont see what i need to add / fix it.

Please advise

  If Len(Me.TextBox2.Value) = 17 Then
    Dim i As Long, x As Long
    Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
    Application.ScreenUpdating = False
    For i = 1 To 8
      ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
    Next i
    
    With ThisWorkbook.Worksheets("MCLIST")
      .Range("A8").EntireRow.Insert Shift:=xlDown
      .Range("A8:K8").Borders.Weight = xlThin
      .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
      If ComboBox3.Value = "HONDA" Then
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
      .Cells(8, 9).Font.Color = vbRed
      Else
      .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
      .Cells(8, 9).Font.Color = vbBlack
      End If
      
      If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
      If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
      If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
      If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
      If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
      If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
      If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
      If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
      If ComboBox3.Value = "HONDA" Then
      ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                 "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
      For i = 0 To UBound(ns)
        If Mid(Range("B8").Value, 10, 1) = ns(i) Then
          Range("I8").Value = "" & 2000 + i
          Exit For
        End If
      Next
      
      Application.EnableEvents = False
      If .AutoFilterMode Then .AutoFilterMode = False
      x = .Cells(.Rows.Count, 1).End(xlUp).Row
      .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
      .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
      Application.Goto Selection, True
    End With
    ActiveWorkbook.Save
    MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Unload McListForm
  Else
    MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
    TextBox2.SetFocus
  End If
  End If
      If Me.ComboBox3.Value = "SUZUKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
       MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
End Sub

 

[mumps]

It looks like you have this "If" line of code:

If ComboBox3.Value = "HONDA" Then

but no "End If" to close it.

 

[ipbr21054]

I did enter it in a place or two but just got worse and then seen different error messages.

 

[MARK858]

Try the code below (untested)

    If Len(Me.TextBox2.Value) = 17 Then
        Dim i As Long, x As Long
        Dim ControlsArr(1 To 8) As Variant, ns As Variant
    
        Application.ScreenUpdating = False
        For i = 1 To 8
            ControlsArr(i) = Controls(IIf(i > 2, "ComboBox", "TextBox") & i).Value
        Next i
    
        With ThisWorkbook.Worksheets("MCLIST")
            .Range("A8").EntireRow.Insert Shift:=xlDown
            .Range("A8:K8").Borders.Weight = xlThin
            .Cells(8, 1).Resize(, UBound(ControlsArr)).Value = ControlsArr
      
            If ComboBox3.Value = "HONDA" Then
                .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbRed
                .Cells(8, 9).Font.Color = vbRed
            Else
                .Cells(8, 2).Characters(Start:=10, Length:=1).Font.Color = vbBlack
                .Cells(8, 9).Font.Color = vbBlack
            End If
      
            If OptionButton1.Value Then .Cells(8, 10).Value = "YES"
            If OptionButton2.Value Then .Cells(8, 10).Value = "NO"
            If OptionButton2.Value Then .Cells(8, 11).Value = "N/A"
            If OptionButton7.Value Then .Cells(8, 11).Value = "BUNDLE"
            If OptionButton8.Value Then .Cells(8, 11).Value = "GREY"
            If OptionButton9.Value Then .Cells(8, 11).Value = "RED"
            If OptionButton10.Value Then .Cells(8, 11).Value = "BLACK"
            If OptionButton11.Value Then .Cells(8, 11).Value = "CLEAR"
      
            If ComboBox3.Value = "HONDA" Then
                ns = Array("X", "Y", "1", "2", "3", "4", "5", "6", "7", "8", "9", "A", "B", "C", _
                           "D", "E", "F", "G", "H", "J", "K", "L", "M", "N", "P", "R", "S")
                For i = 0 To UBound(ns)
                    If Mid(Range("B8").Value, 10, 1) = ns(i) Then
                        Range("I8").Value = "" & 2000 + i
                        Exit For
                    End If
                Next
            End If
        
            Application.EnableEvents = False
            If .AutoFilterMode Then .AutoFilterMode = False
            x = .Cells(.Rows.Count, 1).End(xlUp).Row
            .Range("A7:K" & x).Sort Key1:=.Range("A8"), Order1:=xlAscending, Header:=xlGuess
            .Range("A:A").Find(TextBox1.Value, , xlValues, xlWhole).Select
            Application.Goto Selection, True
        End With
        ActiveWorkbook.Save
        MsgBox "Database Has Been Updated", vbInformation, "SUCCESSFUL MESSAGE"

        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Unload McListForm
    Else
        MsgBox "VIN MUST BE 17 CHARACTERS" & vbCr & vbCr & "DATABASE WAS NOT UPDATED", vbCritical, "MCLIST TRANSFER"
        TextBox2.SetFocus
    End If

    If Me.ComboBox3.Value = "SUZUKI" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "YAMAHA" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    ElseIf Me.ComboBox3.Value = "KAWASAKI" Then
        MsgBox "DONT FORGET TO ADD YEAR", vbInformation, "MOTORCYCLE YEAR MESSAGE"
    End If
End Sub

 

[ipbr21054]

@MARK858 worked a treat thanks

 

[MARK858]

You're welcome. Just a tip but when you make an edit indent your code correctly, it makes it much easier to spot where the error is in these circumstances.

If you find it hard indenting then download a program called Rubberduck, which is where the old Smart Indenter code resides these days.