Creating a new ID for each new entry made

[RobertBon]

Let's say I have a chart as so

<table style="width: 246px; height: 76px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:3401;width:70pt" width="93"> <col style="mso-width-source:userset;mso-width-alt:3145;width:65pt" width="86"> <tbody><tr style="height:12.75pt" height="17"> <td style="height:12.75pt;width:70pt" height="17" width="93">Id Number</td> <td style="width:65pt" width="86">Person Name</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl24" style="height:12.75pt" height="17">0001</td> <td>Ricky</td> </tr> <tr style="height:14.25pt" height="19"> <td class="xl24" style="height:14.25pt" height="19">0002</td> <td>Bobby</td> </tr> </tbody></table>
If I wanted to add a third name, let's say "Junior", would it be possible to automatically populate the Id number based on the previous entry?

Thanks

 

[Smitty]

Welcome to the Board!

This should do what you want:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Worksheet_Change(<SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br>    <SPAN style="color:#007F00">'   Code goes in the Worksheet specific module</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> rng <SPAN style="color:#00007F">As</SPAN> Range<br>        <SPAN style="color:#007F00">'   Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")</SPAN><br>        <SPAN style="color:#00007F">Set</SPAN> rng = Target.Parent.Range("B:B")<br>        <SPAN style="color:#007F00">'   Only look at single cell changes</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Only look at that range</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Intersect(Target, rng) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#007F00">'   Action if Condition(s) are met (do your thing here...)</SPAN><br>            <SPAN style="color:#00007F">If</SPAN> Target.Offset(-1, -1).Value = "ID Number" <SPAN style="color:#00007F">Then</SPAN><br>                Target.Offset(, -1).Value = 1<br>            <SPAN style="color:#00007F">Else</SPAN><br>                Target.Offset(, -1).Value = Target.Offset(-1, -1).Value + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Note that it assumes that you have formatted column A as "0000".

HTH,

 

[RobertBon]

Thanks Smitty,

The code makes sense but I'm getting an "error 13" on the third last line
any ideas?

-R

 

[RobertBon]

The code works flawlessly for any cell that isn't directly under Person name

 

[Smitty]

The code works flawlessly for any cell that isn't directly under Person name

You'll need to make sure that this part ("ID Number") matches your column header exactly.

 

[RobertBon]

Working now,

Thanks a lot for all your help!