VBA Add

[Javi]

Hi All,

The below code is for the add button to fill in the next blank row with the value from the user from. As you can see is start to fill the row starting in column "B" of the 1st avable row.

Colmn "A" is a line number column. Cell A3 has a 1 in it cell A4 is (+A3+1) this formula is copied down column.

I would like add code so column "A" fills in auomatic when the button is clicked. It need to be 1 more then the cell above in column "A". Not sure if I will still need to add the 1 in cell "A3"

Private Sub CMD_Add_Click()
Dim rNextCl As Range

Set rNextCl = Worksheets("Main").Cells(Rows.Count, 2).End(xlUp).Offset(2, 0)
Worksheets("Main").Activate
rNextCl.Select
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Main")
'find first empty row in database
iRow = ws.Cells(Rows.Count, 2) _
.End(xlUp).Offset(1, 0).Row
'check for a part number
'If Trim(Me.branch.Value) = "" Then
'Me.branch.SetFocus
'MsgBox "Please enter a Branch number"
If Me.name = "" Or Me.username = "" Or Me.password = "" Or Me.who = "" Or Me.kind = "" Or Me.site = "" Then
MsgBox ("All Feilds Must be Completed")
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 2).Value = Me.name1.Value
ws.Cells(iRow, 3).Value = Me.username.Value
ws.Cells(iRow, 4).Value = Me.password.Value
ws.Cells(iRow, 5).Value = Me.who.Value
ws.Cells(iRow, 6).Value = Me.kind.Value
ws.Cells(iRow, 7).Value = Me.site.Value
'clear the data
Me.name1.Value = ""
Me.username.Value = ""
Me.password.Value = ""
Me.who.Value = ""
Me.kind.Value = ""
Me.site.Value = ""
End Sub
Private Sub CMD_Close_Click()
Unload Me
End Sub

 

[Sal Paradise]

Add this line to your code:

ws.Cells(iRow, 1).Value = ws.Cells(iRow-1, 1).Value

 

[Javi]

Thank you for the reply.. When I added the line it add the same value as the cell above it did not add the 1.

 

[Sal Paradise]

Woops:

ws.Cells(iRow, 1).Value = ws.Cells(iRow-1, 1).Value + 1

 

[Javi]

THANK YOU !!! Worked Great!!!

Wow I totally forgot to include the format part of my question. I also want to add code to put a border around the row column “A:G”? What do you think?

 

[Sal Paradise]

That's a separate question that requires a separate thread (and is much less simple).

 

[Javi]

Will do thanks for all your help!!!