Generate a next number

rajkumar.shukla

Board Regular
Joined
Oct 10, 2012
Messages
100
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.
 

Some videos you may like

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
You could put this in the sheet's code module.
Code:
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

Board Regular
Joined
Oct 10, 2012
Messages
100
You could put this in the sheet's code module.
Code:
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
 

clabulis

Board Regular
Joined
May 30, 2014
Messages
79

ADVERTISEMENT

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
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

Board Regular
Joined
May 30, 2014
Messages
79

ADVERTISEMENT

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

Code:
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
36,718
Office Version
  1. 2010
Platform
  1. Windows
Actually, yeah I see I misunderstood your request the first time. See if this does what you need:

Code:
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...
Code:
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.
 
Last edited:

rajkumar.shukla

Board Regular
Joined
Oct 10, 2012
Messages
100
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...
Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,123,398
Messages
5,601,440
Members
414,450
Latest member
Cassy_sn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top