simple sequential numbers in VBA

[epardo87]

Hello,

I need to place a simple sequential number in a cell, I searched a lot but couldn't find something as simple as I need:

Cells(emptyRow, 2).Value = ????

Thanks!

 

[njimack]

Are you trying to increment the existing number? If so try this:

With Cells(emptyRow,2)
.Value = .Value+1
End With

 

[epardo87]

Thanks, yes i'm trying to increment existing number. The file already has previous serial numbers which were filled by hand beginning from 1000000.

Every entry is transferred into a new row and I need the serial number to be pasted in the column B

I pasted it just as you wrote it but it only places "1" and doesn't increment it in subsequent entries.

I don't know much but wouldn't we need to make some kind of reference in .value = .value + 1 to use the value of previous row? I'm guessing that is why it keeps giving "1"

 

[njimack]

Which cell currently has the value in?
Which cell are you trying to increment?

 

[epardo87]

last entry has serial number 1000050 in B52, a new entry should place 1000051 in B53

 

[njimack]

last entry has serial number 1000050 in B52, a new entry should place 1000051 in B53

And what does emptyRow equal?

 

[epardo87]

Well, nothing yet, I thought I could place certain code after that for the serial number, like in the other cells ... look here a simplified version of the complete code, everything works fine, I just have this code left to solve (the code I'm asking about is almost at the end):

[COLOR=#0000ff]Private Sub [/COLOR]RegCommandButton_Click()

[COLOR=#0000ff]Dim [/COLOR]emptyRow [COLOR=#0000ff]As Long[/COLOR]

[COLOR=#008000]'Make Sheet2 active[/COLOR]
Sheet2.Activate

[COLOR=#008000]'Determine emptyRow[/COLOR]
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

[COLOR=#008000]'Transfer information[/COLOR]

[COLOR=#0000ff]Dim [/COLOR]bControlEmpty [COLOR=#0000ff]As Boolean[/COLOR]

bControlEmpty = [COLOR=#0000ff]False[/COLOR]

    If Len(ItemComboBox.Value) > 0 [COLOR=#0000ff]Then[/COLOR]
    Cells[B](emptyRow, 3)[/B].Value = ItemComboBox.Value
    [COLOR=#0000ff]Else[/COLOR]
    bControlEmpty = [COLOR=#0000ff]True[/COLOR]
    [COLOR=#0000ff]End If[/COLOR]
    If Len(QtyTextBox.Value) > 0 [COLOR=#0000ff]Then[/COLOR]
    Cells[B](emptyRow, 4)[/B].Value = QtyTextBox.Value
    [COLOR=#0000ff]Else[/COLOR]
    bControlEmpty = [COLOR=#0000ff]True[/COLOR]
    [COLOR=#0000ff]End If[/COLOR]
    If Len(AreaComboBox.Value) > 0 [COLOR=#0000ff]Then[/COLOR]
    Cells[B](emptyRow, 5)[/B].Value = AreaComboBox.Value
    [COLOR=#0000ff]Else[/COLOR]
    bControlEmpty = [COLOR=#0000ff]True[/COLOR]
  [COLOR=#0000ff]  End If[/COLOR]
    If Len(CauseComboBox.Value) > 0 [COLOR=#0000ff]Then[/COLOR]
    Cells[B](emptyRow, 6)[/B].Value = CauseComboBox.Value
    [COLOR=#0000ff]Else[/COLOR]
    bControlEmpty = [COLOR=#0000ff]True[/COLOR]
    [COLOR=#0000ff]End If[/COLOR]
    
[COLOR=#0000ff]If [/COLOR]bControlEmpty [COLOR=#0000ff]Then[/COLOR]
    MsgBox "Please complete the form"
[COLOR=#0000ff]Else[/COLOR]
    
   Cells[B](emptyRow, 1)[/B].Value = Date
[COLOR=#ff0000][B]    With Cells(emptyRow, 2)[/B]
[B]    .Value = .Value + 1[/B]
[B]    End With[/B][/COLOR]

    ThisWorkbook.Save
    Unload Me
    
[COLOR=#0000ff] End If

End Sub[/COLOR]

 

[sericom]

.Value = Cells(emptyRow-1, 2).Value + 1

Does that work?

 

[epardo87]

YES!!! that did it! Thanks a lot! njimack and sericom

Best Regards!!