Solved: Sequential unique numbering in excel

[valendj]

Hi,
I need help creating a sequential unique number in excel using a macro. I would like to be able to double click on any cell in column A and return the next number/text in the sequence JDI1, JDI12, JDI3. I have heard of using a txt document to retrieve the next number but I don’t know how to do that. Any help would be greatly appreciated. I have no experience using VBA and I would need a step-by-step guide. I will donate a minimum of $25 to the site for help. Thank you in advance for any attempts or suggestions.

Thanks

[Bob Phillips]

Try

[vba]

ActiveCell.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
[/vba]

[Kenneth Hobs]

Where will the unique numbers be stored? How will they be used? Could there be gaps in the sequential numbers?

e.g.
A1=Ken10
A2=Ken11
A3=Ken12

If A5 was doubleclicked, would Ken13 be expected?

Considering only A1:A3 as above, if A2 is doubleclicked, would you expect Ken11 to be replaced with Ken11 or Ken13? If Ken13, then Ken11 would be missing?

The Knowledge base has some articles on sequential numbers for filenames. Similar concepts can be used.

[valendj]

@ Kenneth Hobs

I don't care were the unique numbers are stored a text file not sure how to do that..?I would like if clicked Ken11 was replaced with ken13. There can be gaps in the sequential number and the double-click was just a thought. I wouldn't mind a trigger such as if the cell in the next column activated the sequence.

" Where will the unique numbers be stored? How will they be used? Could there be gaps in the sequential numbers?

e.g.
A1=Ken10
A2=Ken11
A3=Ken12

If A5 was doubleclicked, would Ken13 be expected?

Considering only A1:A3 as above, if A2 is doubleclicked, would you expect Ken11 to be replaced with Ken11 or Ken13? If Ken13, then Ken11 would be missing? "

[valendj]

Hi,
Can you provide a step by step? I am really horrible with excel and thank you for your fast response!

Try

[vba]

ActiveCell.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
[/vba]

[valendj]

@ Kenneth Hobs

I don't care were the unique numbers are stored a text file not sure how to do that..?I would like if clicked Ken11 was replaced with ken13. There can be gaps in the sequential number and the double-click was just a thought. I wouldn't mind a trigger such as if the cell in the next column activated the sequence.

Where will the unique numbers be stored? How will they be used? Could there be gaps in the sequential numbers?

e.g.
A1=Ken10
A2=Ken11
A3=Ken12

If A5 was doubleclicked, would Ken13 be expected?

Considering only A1:A3 as above, if A2 is doubleclicked, would you expect Ken11 to be replaced with Ken11 or Ken13? If Ken13, then Ken11 would be missing?

The Knowledge base has some articles on sequential numbers for filenames. Similar concepts can be used.

[valendj]

I put it in Cell A1 but, I get a #Name? error.

Hi,
Can you provide a step by step? I am really horrible with excel and thank you for your fast response!

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End Sub
[/vba]

This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[Kenneth Hobs]

If you have little VBA experience, I doubt that xld's first post would help. To do something like that, you have to add that code and more to the worksheet object as xld as just explained in his 2nd reply. His code works for all cells on that sheet. If you just want one column on that sheet to execute when doubleclicked, it would need modification.

In any case, you have still not answered where the numbers are stored. If you are going to do this for more than one sheet or one workbook and expect to get a sequential number, then indeed an external file is the best route. If it is just by the values in that column for that sheet alone, a solution similar to xld's would work.

[valendj]

Is there a way to have the double click for the unique number work only for column A? If, not is there a way to trigger it by imputing text in column B? Also, can I make the number start at 500? Thanks again for your fast responses you are obviously a genius with excel.

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Target.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End Sub
[/vba]

This is worksheet event code.
To implement it, select the sheet tab, right click, and
select View Code.
Paste this code into the code module that opens in the
VBIDE.
Then close the VBIDE and test it in Excel.

[valendj]

This is only for one sheet and I do need it to work for only column A or triggered by imputing data in column b. Thanks

If you have little VBA experience, I doubt that xld's first post would help. To do something like that, you have to add that code and more to the worksheet object as xld as just explained in his 2nd reply. His code works for all cells on that sheet. If you just want one column on that sheet to execute when doubleclicked, it would need modification.

In any case, you have still not answered where the numbers are stored. If you are going to do this for more than one sheet or one workbook and expect to get a sequential number, then indeed an external file is the best route. If it is just by the values in that column for that sheet alone, a solution similar to xld's would work.

[Bob Phillips]

Is there a way to have the double click for the unique number work only for column A? If, not is there a way to trigger it by imputing text in column B? Also, can I make the number start at 500? Thanks again for your fast responses you are obviously a genius with excel.

Of course

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

Target.Value = "JD" Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End If
End Sub
[/vba]

[valendj]

This does not work. I get a compile / syntax error. The underline is highlighted yellow and the target vaule is red. Please advise.


[VBA]
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

Target.Value = "JD" Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End If
End Sub
[/VBA]

Of course

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

Target.Value = "JD" Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End If
End Sub
[/vba]

[Bob Phillips]

It lost an &

[vba]


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

Target.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End If
End Sub
[/vba]

[valendj]

@XLD
You are great! Thanks you for your help I appriciated every minute you took helpinig me! What took you minutes took my at least 8 hours of trying. Again thank you! Works Great!

It lost an &

[vba]


Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Intersect(Target, Me.Range("A:A")) Is Nothing Then

Target.Value = "JD" & Application.Evaluate("MAX(IF(LEFT(A1:A100,2)=""JD"",--MID(A1:A100,3,99)))") + 1
Cancel = True
End If
End Sub
[/vba]