How to fill the next value when clicked on a cell

[Ananthanand]

Scenario:

Id (needs to entered from handwritten notes)	First Name	Last Name	DOB	Ind
C123
C130
C125
C126
C124
C127
C128
C131
C129

We get an excel sheet everyday with details and need to fill a column called id from handwritten notes.

Is there a way , I can enter an initial value and then click on any cell and successive number is filled there ?

As in, first I would enter initial value 123 and
click on cell A1 it would be 123
click on cell A6 it would fill value 124 and then
click on cell A4 it would fill value 125.

I am open to any suggestions, I am new to VBA and macro and if some one could point me towards commands that could be used there, that would be great too. I have 1500 rows to be filled everyday and would be really thankful if someone could help me out.

 

[Oaktree]

Welcome to the board!

Right click on your sheet tab, go to "view code", and paste this:

Option Explicit

Public NextValue As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" And Target.Column = 1 Then
Target.Value = NextValue
NextValue = NextValue + 1
End If
End Sub

Sub AssignNextValue()
NextValue = Application.InputBox(prompt:="What shall the first number of the counting be?", Title:="First Value", Type:=1)
End Sub

Then, X out of VBA, press alt + F8 to bring up the macro dialog, run the "Sheet1.AssignNextValue" macro, enter the first value as 123, then click away.

A few things to note:
1) You'll want to save the file as either .xlsm or .xlsb format as .xlsx will lose your macro
2) It wasn't obvious if you wanted to save the last value between sessions. If so, the simplest way would be to use a cell as the last update. Both the SelectionChange and the AssignNextValue macros could change the value in that cell as a way of keeping track of the latest value to use. Not sure if we need to go there though.

 

[Ananthanand]

Welcome to the board!

Right click on your sheet tab, go to "view code", and paste this:

Option Explicit

Public NextValue As Integer

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" And Target.Column = 1 Then
Target.Value = NextValue
NextValue = NextValue + 1
End If
End Sub

Sub AssignNextValue()
NextValue = Application.InputBox(prompt:="What shall the first number of the counting be?", Title:="First Value", Type:=1)
End Sub

Then, X out of VBA, press alt + F8 to bring up the macro dialog, run the "Sheet1.AssignNextValue" macro, enter the first value as 123, then click away.

A few things to note:
1) You'll want to save the file as either .xlsm or .xlsb format as .xlsx will lose your macro
2) It wasn't obvious if you wanted to save the last value between sessions. If so, the simplest way would be to use a cell as the last update. Both the SelectionChange and the AssignNextValue macros could change the value in that cell as a way of keeping track of the latest value to use. Not sure if we need to go there though.

Thanks for such a quick response. The Nextvalue is starting as 0 and not getting passed as the input number. I have got the outline and can debug.

I have a request though, how do we set up a break point or end point for the macro. I am not aware of usual end code in such scenarios. Any help would be really great. Thanks again !

 

[Oaktree]

You may have missed the part in my note above about pressing Alt+F8 and running the "Sheet1.AssignNextValue" macro. That will bring up an input box where you would enter the number you want to start with. If you hadn't run that yet, it would default to start at 0.

For your break point, you could change the

If Target.Value = "" And Target.Column = 1 Then

line to be something like

If Target.Value = "" And Target.Column = 1 and NextValue<200 Then

That would ensure you only add values up to 199.