Next without for error userform Excel VBA error with Else-If Statement

Question

I am getting a "Next without For" error which I don't understand.

I have a UserForm with 2 TextBoxes and 1 ComboBox:

When I click Submit button, I want to check if the serial number matches existing data in column 4. In this case, I want the data to fill in the disposition column (3 rows to the right) with text from ComboBox1.

If it does match I want to fill a brand new row.

If no disposition is inputted, I want to exit the sub or message box. Either is ok.

I tried re-arranging If, Else, For, Next but nothing seems to work.

  Private Sub SubmitButton_Click()

  Dim serial_ID As String
  serial_ID = Trim(SN_TextBox1.Text)
  DispValue = ComboBox1.Value

  Worksheets("RMA Tracker").Activate
  lastrow = ActiveSheet.Cells(Rows.Count, 4).End(xlUp).Row

  For i = 2 To lastrow
 

   'Searches for matching RMA & SN 'this assigns data to Log Sheet, if the data is brand new

        If Worksheets("Sheet1").Cells(i, 4).Value <> serial_ID Then
           ActiveSheet.Cells(i + 1, 1) = RMA_TextBox1.Value
           ActiveSheet.Cells(i + 1, 4) = SN_TextBox1.Value
           ActiveSheet.Cells(i + 1, 7) = ComboBox1.Value


        Else
    
   'this assigns data to disposition column to matching entries in serial number column

         If Worksheets("Sheet1").Cells(i, 4).Value = serial_ID Then
         ComboBox1.Text = Worksheets("Sheet1").Cells(i, 7).Value


         Else
   
   
         If DispValue = "" Then
         Exit Sub
  

         End If

 Next i

 'this clears the fields of userform when button is clicked and saves it automatically
 ActiveWorkbook.Save
 Call resetform


  End Sub

try selecting all the code, then press Shift+Tab until everything sits on the left margin - then select every line between Sub and End Sub, hit Tab; then select every line between For and Next i, and successively indent entire nested code blocks like this, until you eventually hit the inevitable "oh." and add the missing End If. The best way to avoid this compile error is to consistently close every block you open, as soon as it's opened: type If {condition} Then<ENTER>, follow it with End If immediately and insert the body (at +1 indent level) - that will never fail you =) — Mathieu Guindon
– Mathieu Guindon, Commented Jul 27, 2020 at 22:20
@MathieuGuindon Or install Rubberduck and use the auto indent feature :-). — Michael Wycisk
– Michael Wycisk, Commented Jul 27, 2020 at 23:04

Michael Wycisk · Accepted Answer · 2020-07-27 21:50:48Z

The error message is a bit misleading. The problem are your If statements. You have three If statements, but only one End If statement. Since you also have two Else statements, I assume, you want an If / ElseIf structure. There are two ways to fix that.

Three separate IF statements (all three statements will be executed)

         If Worksheets("Sheet1").Cells(i, 4).Value <> serial_ID Then
            ActiveSheet.Cells(i + 1, 1) = RMA_TextBox1.Value
            ActiveSheet.Cells(i + 1, 4) = SN_TextBox1.Value
            ActiveSheet.Cells(i + 1, 7) = ComboBox1.Value
         End if

    'this assigns data to disposition column to matching entries in serial number column

          If Worksheets("Sheet1").Cells(i, 4).Value = serial_ID Then
              ComboBox1.Text = Worksheets("Sheet1").Cells(i, 7).Value
          End if

          If DispValue = "" Then
             Exit Sub
          End If

Alternatively, you can set up an If / ElseIf structure (if the first/second If statement is True, the subsequent If statements won't be reached and code execution will proceed after the End If statement - unless you reach Exit Sub of course) :

          If Worksheets("Sheet1").Cells(i, 4).Value <> serial_ID Then
              ActiveSheet.Cells(i + 1, 1) = RMA_TextBox1.Value
              ActiveSheet.Cells(i + 1, 4) = SN_TextBox1.Value
              ActiveSheet.Cells(i + 1, 7) = ComboBox1.Value
          ElseIf Worksheets("Sheet1").Cells(i, 4).Value = serial_ID Then
              ComboBox1.Text = Worksheets("Sheet1").Cells(i, 7).Value
          ElseIf DispValue = "" Then
             Exit Sub
          End If

This certainly did the trick and got rid of the error. Thank you very much! I tried both ways..

Collectives™ on Stack Overflow

Next without for error userform Excel VBA error with Else-If Statement

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related