Autonumber in excel userform

[Smalleyfamily]

Hi,

I have a useform that I use to update a spreadsheet. What I would like to do is add an autonumber in column A (on a sheet called "BACS") when the data is saved "CmdSave".

The autonumber should be sequential, e.g. if the autonumber on the last entry is 56, then the autonumber for the new entry should be 57.

I hope you can help.

Regards,

Grant

 

[VoG]

Try

Dim LR As Long
With Sheets("BACS")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Range("A" & LR).Value + 1
End With

 

[Smalleyfamily]

Hi,

I've tried the code but with no joy. To be honest I am not sure where to place the code? Below is an extract of the code, can someone let me know where to add it?

P.S. I realised i'd need to look up column Q (15) rather than A and have changed the code that was provided but with no joy.

Private Sub Create_Click()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o> </o>
Call UserForm_Initialize<o></o>
<o> </o>
End Sub<o></o>
<o> </o>
<o> </o>
<o> </o>
Private Sub cmdSubmit_Click()<o></o>
<o> </o>
Message.Visible = True<o></o>
<o> </o>
ActiveWorkbook.Sheets("BACS").Activate<o></o>
<o> </o>
Range("B2").Select<o></o>
<o> </o>
Do<o></o>
<o> </o>
If IsEmpty(ActiveCell) = False Then<o></o>
<o> </o>
ActiveCell.Offset(1, 0).Select<o></o>
<o> </o>
End If<o></o>
<o> </o>
Loop Until IsEmpty(ActiveCell) = True<o></o>
<o> </o>
ActiveCell.Value = txtSort.Value<o></o>
<o></o>
ActiveCell.Offset(0, 1) = txtAccount.Value<o></o>
<o> </o>
ActiveCell.Offset(0, 2) = txtAmount.Value<o></o>
<o></o>
ActiveCell.Offset(0, 3) = "<?xml:namespace prefix = st1 ns = "urn:schemas-microsoft-comffice:smarttags" /><st1lace w:st="on">Mobile</st1lace> Device Ins"<o></o>
<o></o>
ActiveCell.Offset(0, 5) = txtCusname.Value<o></o>
<o></o>
ActiveCell.Offset(0, 4) = txtPolno.Value<o></o>
<o></o>
ActiveCell.Offset(0, 6) = PolicyType.Value<o></o>
<o></o>
ActiveCell.Offset(0, 7) = cboBusp.Value<o></o>
<o></o>
ActiveCell.Offset(0, 8) = BP.Value<o></o>
<o></o>
ActiveCell.Offset(0, 9) = cboChequeReason.Value<o></o>
<o> </o>
ActiveCell.Offset(0, 10) = Authorisation.Value<o></o>
<o> </o>
ActiveCell.Offset(0, 11) = Make.Value<o></o>
<o> </o>
ActiveCell.Offset(0, 12) = Model.Value<o></o>
<o></o>
ActiveCell.Offset(0, 13) = fOSUserName<o></o>
<o> </o>
ActiveCell.Offset(0, 14) = Now()<o></o>
<o></o>
<o> </o>
<o> </o>
<o> </o>
End Sub<o></o>

 

[VoG]

Try

Private Sub cmdSubmit_Click()
Dim LR As Long
Message.Visible = True
With Sheets("BACS")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A" & LR + 1).Value = Range("A" & LR).Value + 1
    LR = .Range("B" & Rows.Count).End(xlUp).Row + 1
    .Cells(LR, 2).Value = txtSort.Value
    .Cells(LR, 3).Value = txtAccount.Value
    .Cells(LR, 4).Value = txtAmount.Value
    .Cells(LR, 5).Value = "Mobile Device Ins"
    .Cells(LR, 6).Value = txtCusname.Value
    .Cells(LR, 7).Value = txtPolno.Value
    .Cells(LR, 8).Value = PolicyType.Value
    .Cells(LR, 9).Value = cboBusp.Value
    .Cells(LR, 10).Value = BP.Value
    .Cells(LR, 11).Value = cboChequeReason.Value
    .Cells(LR, 12).Value = Authorisation.Value
    .Cells(LR, 13).Value = Make.Value
    .Cells(LR, 14).Value = Model.Value
    .Cells(LR, 15).Value = fOSUserName
    .Cells(LR, 16).Value = Now
End With
End Sub

 

[Smalleyfamily]

Hi VoG,

It works a treat; I really appreciate your help.

Thanks

Grant