(Solved) – Incorrect result filling a table out with a loop


I have a worksheet with three tables that I want to fill out with a random sample of cells from another sheet.

When going through the loop, Table1 fills out correctly while Table2 and Table3 have alternate results (meaning on each row just one table is filled out). The code is the same on each case so I’m not sure what the issue is.


Sub Test()

With Sheets("600 mots")
    All_start_row = 3
    Adj_end_row = .Cells(.Rows.Count, "A").End(xlUp).Row
    Nom_end_row = .Cells(.Rows.Count, "I").End(xlUp).Row
    Verb_end_row = .Cells(.Rows.Count, "E").End(xlUp).Row

For i = 1 To 50
    adj_exer = WorksheetFunction.RandBetween(All_start_row, Adj_end_row)
    Adj_LR = Worksheets("Ejercicios").ListObjects("Table1").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("600 mots").Range("A" & adj_exer).Copy Destination:=Sheets("Ejercicios").Range("A" & Adj_LR   1)

    verb_exer = WorksheetFunction.RandBetween(All_start_row, Verb_end_row)
    Ver_LR = Worksheets("Ejercicios").ListObjects("Table2").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("600 mots").Range("E" & verb_exer).Copy Destination:=Sheets("Ejercicios").Range("D" & Ver_LR   1)

    nom_exer = WorksheetFunction.RandBetween(All_start_row, Nom_end_row)
    Nom_LR = Worksheets("Ejercicios").ListObjects("Table3").Range.Columns(1).Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Sheets("600 mots").Range("I" & nom_exer).Copy Destination:=Sheets("Ejercicios").Range("G" & Nom_LR   1)
Next i

End With
End sub

Leave a Reply

Your email address will not be published. Required fields are marked *