Insert same value for many rows, depends on number

[NDMDRB]

Hello,

I have a userform that contains a lot of controls

One of them is "cmdSubmit" that contains many actions (I finished them all instead the following)

I already have piece of code that copy a range and paste it in sheet2 (this range contains (Name, ID, ...)(Doesn't contain the Number)
ex: I copied the range and pate it in sheet2 B4 (using the code below)

ws.Range("DailySales_SalesList").Copy
    With Sheet2.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0)
        .PasteSpecial xlPasteValues
        .PasteSpecial xlPasteFormats
        Application.CutCopyMode = False
    End With

What I missed is:

A piece of code to inset the Number in Column "A" Row (Depends on the name count)
A4, A5, A6 should be (Sheet3.Cells(Rows.Count, 1).End(xlUp).Value + 1) ex: 234

I tried the following code but I got an error

Set c = ws.Range("I:I").Find(What:=Me.txt_Name1.Value, LookIn:=xlValues)
a = Sheet2.Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To Application.WorksheetFunction.CountIf(ws.Range("I:I"), c)
a.Offset(i, 0) = Sheet3.Cells(Rows.Count, 1).End(xlUp).Value + 1
Next i

I need your help please and many thanks in advance


Sheet2
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Number[/TD]
[TD="align: center"]Name[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]222[/TD]
[TD="align: center"]Pop[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]222[/TD]
[TD="align: center"]Pop[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Adam[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

​

 

[NDMDRB]

I did it, thank you