Search code examples
excelvbacombinationsmultiple-columns

VBA error code : "For control variable already in use" for combination execution

Thank you so much @ chris neilsen, Siddharth Rout, FunThomas, user10186832 for making me realize how to put out question. Yes, I am preety naive to VBA in excel. I apologies for the mistake. I am trying to do following:

enter image description here

Where, B2:B13 is points and C2:C13 is number of repeats. I want to have pattern as given F, J an N columns. I made the code based on the web surfing and try to do the same. Im getting an error message saying control variable already in use. When the error pops up the second Sub combinations() is highlighted in the VBA editor. I trying to get combinations in different columns and eliminating the rows which has blank cell in column. Hope I am able to explain. Can anyone help correcting this code. Thank you

Sub combinations()
i = 1
For Each cella In Sheet3.Range("A:A").SpecialCells(xlCellTypeConstants)
    For Each cellb In Sheet3.Range("B:B").SpecialCells(xlCellTypeConstants)
        For Each cellc In Sheet3.Range("C:C").SpecialCells(xlCellTypeConstants)
            For Each cellc In Sheet3.Range("D:D").SpecialCells(xlCellTypeConstants)
                For Each cellc In Sheet3.Range("E:E").SpecialCells(xlCellTypeConstants)
                    For Each cellc In Sheet3.Range("F:F").SpecialCells(xlCellTypeConstants)
                        For Each cellc In Sheet3.Range("G:G").SpecialCells(xlCellTypeConstants)
                            For Each cellc In Sheet3.Range("H:H").SpecialCells(xlCellTypeConstants)
                                For Each cellc In Sheet3.Range("I:I").SpecialCells(xlCellTypeConstants)
                                    For Each cellc In Sheet3.Range("J:J").SpecialCells(xlCellTypeConstants)
                                        For Each cellc In Sheet3.Range("K:K").SpecialCells(xlCellTypeConstants)
                                            For Each cellc In Sheet3.Range("L:L").SpecialCells(xlCellTypeConstants)
                                                For Each cellc In Sheet3.Range("M:M").SpecialCells(xlCellTypeConstants)
                                                    Sheet9.Range("A" & i).Value = cella.Value
                                                    Sheet9.Range("B" & i).Value = cellb.Value
                                                    Sheet9.Range("C" & i).Value = cellc.Value
                                                    Sheet9.Range("D" & i).Value = cellc.Value
                                                    Sheet9.Range("E" & i).Value = cellc.Value
                                                    Sheet9.Range("F" & i).Value = cellc.Value
                                                    Sheet9.Range("G" & i).Value = cellc.Value
                                                    Sheet9.Range("H" & i).Value = cellc.Value
                                                    Sheet9.Range("I" & i).Value = cellc.Value
                                                    Sheet9.Range("J" & i).Value = cellc.Value
                                                    Sheet9.Range("K" & i).Value = cellc.Value
                                                    Sheet9.Range("L" & i).Value = cellc.Value
                                                    Sheet9.Range("M" & i).Value = cellc.Value
                                                    i = i + 1
                                                Next
                                            Next
                                        Next
                                    Next
                                Next
                            Next
                        Next
                    Next
                Next
            Next
        Next
    Next
Next

Set mysheet1 = Sheet9
    row1 = mysheet1.UsedRange.Rows.Count
    For i = row1 To 1 Step -1
    If Len(mysheet1.Cells(i, 1).Value) = 0 Then
      mysheet1.Cells(i, 1).EntireRow.Delete
    End If
Next

End Sub

Solution

  • You could shorten up your loop to delete lines with one line of code:

    On Error Resume Next
Sheet9.Columns(1).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0

    This deletes every line where cell A is empty. The suppressed error handling is needed because if there is no empty cell in column A an error would occur.

    I'm not sure what you are trying to do with that horrible nested for-loops. But the others told you already in the comments that this might fail. At least you have to name the loop variables unique, but I doubt that this will solve the problem.