Insert validation list in multiple rows using VBA

Question

I'm making a spreadsheet to control the finances of my office. I made a VBA code where I enter 4 different data: i) Client's name; ii) Total value of the contract; iii) Number of months that the contract will be paid; iv) Date of the first payment.

Depending on the number of months that the payment will be made (iii), the code inserts the same number of rows. With the help of @Paster (link to question) I was able to do that.

Now I have a new question: In every new row, I want to have a validation list (yes/no) on column 6, where I can control if the payment was made or not. I was able to do that when there is only 1 row added, but I don't know how to add when "If .Cells(iRow, 5) > 1 Then" kicks in.

I want it to look like this:

Client	Value	Date	Control
John	100	01/01/2020	Yes/No
John	100	02/01/2020	Yes/No
Claire	500	01/05/2020	Yes/No

I'm still learning VBA and I just can't figure out.

The actual code is:

Private Sub cmdAdd_Click()

Dim iRow As Long
Dim ws As Worksheet
Dim Name As String
Dim counter As Integer
Dim money As Double
Dim Data As Date
Dim i As Integer

Set ws = Worksheets("Projetos")

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
    SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1
    
'copy the data to the database
With ws
  .Cells(iRow, 2).Value = Me.boxCliente.Value 'Client info
  .Cells(iRow, 3) = CCur(boxValor.Value) 'Value
  .Cells(iRow, 5).Value = Me.boxParcela.Value '# of payments
  .Cells(iRow, 4) = CDate(boxData.Value) 'Date

'add validation list to row
  With Cells(iRow, 6).Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
    xlBetween, Formula1:="Não,Sim"
    .IgnoreBlank = True
    .InCellDropdown = True
    .InputTitle = ""
    .ErrorTitle = ""
    .InputMessage = ""
    .ErrorMessage = ""
    .ShowInput = True
    .ShowError = True
    End With
    Cells(iRow, 6).Value = "Não"
  
'if multiple payments, then
  If .Cells(iRow, 5) > 1 Then
    Name = .Cells(iRow, 2).Value
    counter = .Cells(iRow, 5).Value
    money = .Cells(iRow, 3).Value
    Data = .Cells(iRow, 4).Value
    
    For i = 0 To counter - 1
        .Cells(iRow + i, 2).Value = Name
        .Cells(iRow + i, 3).Value = money / counter
        .Cells(iRow + i, 4).Value = Format(DateAdd("m", i, Data), "mm/dd/yyyy")
    Next i
  End If
End With

'clear the data
Me.boxCliente.Value = ""
Me.boxValor.Value = ""
Me.boxParcela.Value = ""
Me.boxData.Value = ""

End Sub

Answer 1

Take the same validation logic that you have for the one row, and also include it in the loop where you copy values to each row (For i = 1 to counter - 1)

For i = 0 To counter - 1
    .Cells(iRow + i, 2).Value = Name
    .Cells(iRow + i, 3).Value = money / counter
    .Cells(iRow + i, 4).Value = Format(DateAdd("m", i, Data), "mm/dd/yyyy")

    'add validation list to row
    With Cells(iRow, 6).Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
            xlBetween, Formula1:="Não,Sim"
        .IgnoreBlank = True
        .InCellDropdown = True
        .InputTitle = ""
        .ErrorTitle = ""
        .InputMessage = ""
        .ErrorMessage = ""
        .ShowInput = True
        .ShowError = True
    End With
    Cells(iRow, 6).Value = "Não"
Next i