Numeros variables in the Loop with UserFoam

[BluEEyE86]

Hello,

I have a below user form when I have some data in two columns:

First column is marked with viariables from Roll1val to Roll10val. Second colun accordingly: From Roll1 to Roll10. I'd like to write data in excell file row by row like with conditional check if in next row there are some data. If yes, write it in next row in excell sheet. If not, stop the macro. Can you help me as I don't know yet how to make variables in the loop ?

1st row: Roll1val , Roll1
2nd row: Roll2Val , Roll2

Private Sub SubmitButton_Click()

    Dim sh As Worksheet
    Dim RollNo As String
    Dim iRow As Long
           
    Set sh = ThisWorkbook.Sheets("Database")
       
    RollNo = ExtraTest.Rolli
           
    If Not RollNo Is Nothing Then
           
            For i = 1 To 10
                
                    iRow = [Counta(Database!A:A)] + 1
                    
                    With sh
                        
                        .Cells(iRow, 1) = iRow - 1
                        
                        .Cells(iRow, 2) = ExtraTest.MFGNo.Value
                               
                        .Cells(iRow, 3) = ExtraTest.FS
                                
                        .Cells(iRow, 4) = ExtraTest.Fname
                        
                        .Cells(iRow, 6) = "Dodatkowe testy"
                                
                        .Cells(iRow, 7) = ExtraTest.Rollival
                        
                        .Cells(iRow, 8) = [Text(Now(), "MM/DD/YYYY HH:MM")]
                                        
                        .Cells(iRow, 10) = "Otwarte"
                        
                        .Cells(iRow, 19) = "Dodatkowe testy"
                        
                        .Cells(iRow, 20) = ExtraTest.Testi
                    
                    End With
            
            Next i
            
    End If
            
    MsgBox ("Nowe zlecenie zostało zarejestrowane")

End Sub

 

[BluEEyE86]

Already sorted with tables.

 

[Fluff]

Glad to hear you got the solution.

If you would like to post the solution then it is perfectly fine to mark your post as the solution to help future readers. Otherwise, please do not mark a post that doesn't contain a solution.

 

[BluEEyE86]

Apologize, Here is my solution. It's not perfect & I guess can be easily improved but it works for my needs.

rivate Sub SubmitButton_Click()

    Dim sh As Worksheet
    Dim RollNo As String
    Dim iRow As Long
    Dim i As Long
    Dim addtests(1 To 2, 1 To 10) As String
    Dim msgValue As VbMsgBoxResult
               
    Set sh = ThisWorkbook.Sheets("Database")
                  
            
                addtests(1, 1) = ExtraTest.Roll1
                addtests(1, 2) = ExtraTest.Roll2
                addtests(1, 3) = ExtraTest.Roll3
                addtests(1, 4) = ExtraTest.Roll4
                addtests(1, 5) = ExtraTest.Roll5
                addtests(1, 6) = ExtraTest.Roll6
                addtests(1, 7) = ExtraTest.Roll7
                addtests(1, 8) = ExtraTest.Roll8
                addtests(1, 9) = ExtraTest.Roll9
                addtests(1, 10) = ExtraTest.Roll10
                
                addtests(2, 1) = ExtraTest.cbTest1
                addtests(2, 2) = ExtraTest.cbTest2
                addtests(2, 3) = ExtraTest.cbTest3
                addtests(2, 4) = ExtraTest.cbTest4
                addtests(2, 5) = ExtraTest.cbTest5
                addtests(2, 6) = ExtraTest.cbTest6
                addtests(2, 7) = ExtraTest.cbTest7
                addtests(2, 8) = ExtraTest.cbTest8
                addtests(2, 9) = ExtraTest.cbTest9
                addtests(2, 10) = ExtraTest.cbTest10
                
                For i = 1 To 10
           
                    If addtests(1, i) <> "" Then
                
                        iRow = [Counta(Database!A:A)] + 1
                        
                        With sh
                            
                            .Cells(iRow, 1) = iRow - 1
                            
                            .Cells(iRow, 2) = ExtraTest.MFGNo.Value
                                   
                            .Cells(iRow, 3) = ExtraTest.FS
                                    
                            .Cells(iRow, 4) = ExtraTest.Fname
                            
                            .Cells(iRow, 6) = "Dodatkowe testy"
                                    
                            .Cells(iRow, 7) = addtests(1, i)
                            
                            .Cells(iRow, 8) = [Text(Now(), "MM/DD/YYYY HH:MM")]
                                            
                            .Cells(iRow, 10) = "Otwarte"
                            
                            .Cells(iRow, 11) = ExtraTest.cbRegister
                            
                            .Cells(iRow, 14).Value = Application.WorksheetFunction.IsoWeekNum(.Cells(iRow, 8).Value)
                            
                            .Cells(iRow, 19) = "Dodatkowe testy"
                            
                            .Cells(iRow, 20) = addtests(2, i)
                        
                        End With
                    
                    End If
            Next i
            
    msgValue = MsgBox("Nowe zlecenie zostało zarejestrowane. Czy chcesz dodac jeszcze jakies testy ?", vbYesNo + vbInformation, "Confirmation")
    
    If msgValue = vbNo Then
        
        ExtraTest.Hide
        Exit Sub
         
    End If

End Sub