Generate a next number

[rajkumar.shukla]

Hi frnds,

I want a simple vba for generate a next number in below the cell. for example. if I enter any data in B1, than vba generate automatically 1 in A1 cell than i enter a data in cell b2 vba generate a next number (ie 2) in cell A2.

 

[mikerickson]

You could put this in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Range("A1").Value = Val(CStr(Range("A1").Value)) + 1
    End If
End Sub

 

[rajkumar.shukla]

You could put this in the sheet's code module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Range("A1").Value = Val(CStr(Range("A1").Value)) + 1
    End If
End Sub

this code works only once. I want whenever I enter the data in next cell VBA generate the next number like when I enter the text or anything in cell B1 VBA generate next number in A1(i.e.1) than when I enter the data in next cell B2 vba generate next number in A2 (which is 2) and so on

 

[rajkumar.shukla]

mikerickson plz help. I want your help in this.

 

[clabulis]

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$1" Then
        Range("A1").Value = Range("A1").Value[FONT=Verdana] + 1[/FONT]
    End If
End Sub

 

[Rick Rothstein]

Hi frnds,

I want a simple vba for generate a next number in below the cell. for example. if I enter any data in B1, than vba generate automatically 1 in A1 cell than i enter a data in cell b2 vba generate a next number (ie 2) in cell A2.

What should happen if a row in Column B is skipped? For example, the user enters something in cell B1 first, then skips over cell B2 to enter something in cell B3 next... should cell A3 get the 2? If yes, what should happen if the user next goes back to the skipped cell and puts something in cell B2... should A2 get a 3 placed in it or should the cells in Column A renumber into the proper sequence even though the numbers won't correspond to the order of entry?

 

[clabulis]

Actually, yeah I see I misunderstood your request the first time. See if this does what you need:

Private Sub Worksheet_Change(ByVal Target As Range)


[FONT=courier new]    If Target.Column = 2 Then[/FONT]
[FONT=courier new]        On Error Resume Next[/FONT]
[FONT=courier new]        Cells(Target.Row, 1).Value = Target.Row[/FONT]
[FONT=courier new]    End If[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]End Sub[/FONT]

 

[Rick Rothstein]

Actually, yeah I see I misunderstood your request the first time. See if this does what you need:

Private Sub Worksheet_Change(ByVal Target As Range)


[FONT=courier new]    If Target.Column = 2 Then[/FONT]
[FONT=courier new]        On Error Resume Next[/FONT]
[FONT=courier new]        Cells(Target.Row, 1).Value = Target.Row[/FONT]
[FONT=courier new]    End If[/FONT]
[FONT=courier new]    [/FONT]
[FONT=courier new]End Sub[/FONT]

First, why the "On Error Resume Next" statement? I don't see anything that should generate an error.

Second, the change event code actually executes twice as written... the first time in response to the user's entry and the second time in response to your code entering a value in Column A (your code doesn't actually do anything physically on the second go-round because the change is in the wrong column). While it is not all that critical for this small piece of code, better technique for Change event code is to disable events before the code starts and re-enable them afterwards. For your code (and removing the "On error" line...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Cells(Target.Row, 1).Value = Target.Row
        Application.EnableEvents = True
    End If
End Sub

Third, you code may or may not do what the OP wants depending on his answer to the question(s) I asked in Message #6.

 

[rajkumar.shukla]

First, why the "On Error Resume Next" statement? I don't see anything that should generate an error.

Second, the change event code actually executes twice as written... the first time in response to the user's entry and the second time in response to your code entering a value in Column A (your code doesn't actually do anything physically on the second go-round because the change is in the wrong column). While it is not all that critical for this small piece of code, better technique for Change event code is to disable events before the code starts and re-enable them afterwards. For your code (and removing the "On error" line...

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 2 Then
        Application.EnableEvents = False
        Cells(Target.Row, 1).Value = Target.Row
        Application.EnableEvents = True
    End If




End Sub

Third, you code may or may not do what the OP wants depending on his answer to the question(s) I asked in Message #6.

thanks to all my frnnds. code provided by you solve my purpose. thanks for your help

 

[rajkumar.shukla]

thanks to all my frnnds. code provided by you solve my purpose. thanks for your help

Hi this code run perfectly. but one thing which is not correct. It start with A1. in row 1 contain my heading. I want this code run from the row 2