How to get VBA to automatically generate Column Index numbers

Hello,

I am working with a group of individuals that will be passing around an
excel spreadsheet to one another, and wanted to come up with a way to have
the 1st column act as an index, with the key component requiring that the
index column would automatically re-number itself, if someone entered a new
row.

A typical value in the first column looks like this: 8-5-012-005

Which in our case means that there are 4 series of number sets, separated by
dashes. So the first set is 8, the second 5, the third 012, and the fourth
005. The available range for the sets are 7 or 8 for the first, 5 through 9
for the second, and 0 through 130, and 0 through 200, respectively.

So the user can pick any of these ranges for when they decide to create a
new record (row).

Here is the way the spreadsheet columns currently look (always sorted by
Tract_ID):

Row-1 Tract_ID Parcel_ID
Row-2 7-5-065-105 01245787
Row-3 7-5-112-005 01245787
Row-4 8-5-012-005 01245787
Row-5 8-6-030-125 01245787

Now, here is the way I'd like to have the spreadsheet columns look with the
Index_No (can be either Numeric or Text - depending on your recommendations).
The sort order is based on 1st, the index number, then 2nd the Tract_ID:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-112-005 0126A560
Row-4 3 8-5-012-005 01005147
Row-5 4 8-6-030-125 01000541

Then, let's say the user wants to enter a new value like say, 7-5-105-021.
That value would need to go between Row-1 and Row-2, which, if they just
inserted the value in the row of their choice, would screw up the indexing.

What I need is a way to ALWAYS create an index (automatically), no matter
where they decide to put the value in the spreadsheet, AND it would update
all of the other Indexes as well (very important requirement).

So the end result would be this:

Row-1 Index_No Tract_ID Parcel_ID
Row-2 1 7-5-065-105 01245787
Row-3 2 7-5-105-021 00547419
Row-4 3 7-5-112-005 5126A560
Row-5 4 8-5-012-005 00005147
Row-6 5 8-6-030-125 00001541

If you need more information, please let me know.

TIA for your replies.

Phil.

BTW, if you are noticing that this was posted in another MS NG, you'd be
correct, but I have decided to post here as well, as one of the respondents
commented that this might be better done with VBA.

Sub BBB()
Dim rng As Range
With ActiveSheet
Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
End With
Set rng = rng.Offset(0, -1)
rng(1) = 1
rng(2) = 2
rng(1).Resize(2, 1).AutoFill rng
End Sub

Just run it after you have inserted your new row and have completed entering
your new data.

--
Regards,
Tom Ogilvy




"Phil" <Phil@discussions.microsoft.com> wrote in message
news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> Hello,
>
> I am working with a group of individuals that will be passing around an
> excel spreadsheet to one another, and wanted to come up with a way to have
> the 1st column act as an index, with the key component requiring that the
> index column would automatically re-number itself, if someone entered a
new
> row.
>
> A typical value in the first column looks like this: 8-5-012-005
>
> Which in our case means that there are 4 series of number sets, separated
by
> dashes. So the first set is 8, the second 5, the third 012, and the fourth
> 005. The available range for the sets are 7 or 8 for the first, 5 through
9
> for the second, and 0 through 130, and 0 through 200, respectively.
>
> So the user can pick any of these ranges for when they decide to create a
> new record (row).
>
> Here is the way the spreadsheet columns currently look (always sorted by
> Tract_ID):
>
> Row-1 Tract_ID Parcel_ID
> Row-2 7-5-065-105 01245787
> Row-3 7-5-112-005 01245787
> Row-4 8-5-012-005 01245787
> Row-5 8-6-030-125 01245787
>
> Now, here is the way I'd like to have the spreadsheet columns look with
the
> Index_No (can be either Numeric or Text - depending on your
recommendations).
> The sort order is based on 1st, the index number, then 2nd the Tract_ID:
>
> Row-1 Index_No Tract_ID Parcel_ID
> Row-2 1 7-5-065-105 01245787
> Row-3 2 7-5-112-005 0126A560
> Row-4 3 8-5-012-005 01005147
> Row-5 4 8-6-030-125 01000541
>
> Then, let's say the user wants to enter a new value like say, 7-5-105-021.
> That value would need to go between Row-1 and Row-2, which, if they just
> inserted the value in the row of their choice, would screw up the
indexing.
>
> What I need is a way to ALWAYS create an index (automatically), no matter
> where they decide to put the value in the spreadsheet, AND it would update
> all of the other Indexes as well (very important requirement).
>
> So the end result would be this:
>
> Row-1 Index_No Tract_ID Parcel_ID
> Row-2 1 7-5-065-105 01245787
> Row-3 2 7-5-105-021 00547419
> Row-4 3 7-5-112-005 5126A560
> Row-5 4 8-5-012-005 00005147
> Row-6 5 8-6-030-125 00001541
>
> If you need more information, please let me know.
>
> TIA for your replies.
>
> Phil.
>
> BTW, if you are noticing that this was posted in another MS NG, you'd be
> correct, but I have decided to post here as well, as one of the
respondents
> commented that this might be better done with VBA.
>

Tom,

Your solution worked perfectly. Thank you.

However, after running it, I realized that the routine will not reveal the
newest records (when I get the spreadsheet back from the field people), and
therefore, I need to modify the requirement parameters.

What I need instead, is after the user enters a new row for the record, the
routine finds the last index number, then increment from the last number +1,
and place it in the blank Index_No cell next to the new record.

So, it would be like this:

Index_No
r2 1
....
r526 525

And if the user wanted to insert a new record at say, row 500, then the new
Index_No for that record would be 526.

That way, when I get the spreadsheet back, I can see instantly (after doing
a sort) where all the new records are.

Also, is there a way to incorporate the macro so that it will:

a) do the insert row as well using maybe the "Selection.EntireRow.Insert"
command, AND

b) place the cursor in the 1st column to the right of the new index number?

What do you think?

Thanks again.

Phil.

"Tom Ogilvy" wrote:

> Sub BBB()
> Dim rng As Range
> With ActiveSheet
> Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
> End With
> Set rng = rng.Offset(0, -1)
> rng(1) = 1
> rng(2) = 2
> rng(1).Resize(2, 1).AutoFill rng
> End Sub
>
> Just run it after you have inserted your new row and have completed entering
> your new data.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
>
> "Phil" <Phil@discussions.microsoft.com> wrote in message
> news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> > Hello,
> >
> > I am working with a group of individuals that will be passing around an
> > excel spreadsheet to one another, and wanted to come up with a way to have
> > the 1st column act as an index, with the key component requiring that the
> > index column would automatically re-number itself, if someone entered a
> new
> > row.
> >
> > A typical value in the first column looks like this: 8-5-012-005
> >
> > Which in our case means that there are 4 series of number sets, separated
> by
> > dashes. So the first set is 8, the second 5, the third 012, and the fourth
> > 005. The available range for the sets are 7 or 8 for the first, 5 through
> 9
> > for the second, and 0 through 130, and 0 through 200, respectively.
> >
> > So the user can pick any of these ranges for when they decide to create a
> > new record (row).
> >
> > Here is the way the spreadsheet columns currently look (always sorted by
> > Tract_ID):
> >
> > Row-1 Tract_ID Parcel_ID
> > Row-2 7-5-065-105 01245787
> > Row-3 7-5-112-005 01245787
> > Row-4 8-5-012-005 01245787
> > Row-5 8-6-030-125 01245787
> >
> > Now, here is the way I'd like to have the spreadsheet columns look with
> the
> > Index_No (can be either Numeric or Text - depending on your
> recommendations).
> > The sort order is based on 1st, the index number, then 2nd the Tract_ID:
> >
> > Row-1 Index_No Tract_ID Parcel_ID
> > Row-2 1 7-5-065-105 01245787
> > Row-3 2 7-5-112-005 0126A560
> > Row-4 3 8-5-012-005 01005147
> > Row-5 4 8-6-030-125 01000541
> >
> > Then, let's say the user wants to enter a new value like say, 7-5-105-021.
> > That value would need to go between Row-1 and Row-2, which, if they just
> > inserted the value in the row of their choice, would screw up the
> indexing.
> >
> > What I need is a way to ALWAYS create an index (automatically), no matter
> > where they decide to put the value in the spreadsheet, AND it would update
> > all of the other Indexes as well (very important requirement).
> >
> > So the end result would be this:
> >
> > Row-1 Index_No Tract_ID Parcel_ID
> > Row-2 1 7-5-065-105 01245787
> > Row-3 2 7-5-105-021 00547419
> > Row-4 3 7-5-112-005 5126A560
> > Row-5 4 8-5-012-005 00005147
> > Row-6 5 8-6-030-125 00001541
> >
> > If you need more information, please let me know.
> >
> > TIA for your replies.
> >
> > Phil.
> >
> > BTW, if you are noticing that this was posted in another MS NG, you'd be
> > correct, but I have decided to post here as well, as one of the
> respondents
> > commented that this might be better done with VBA.
> >
>
>
>

Phil:
May I suggest a completely different approach?

I think I would set up a userform for input, then would have the macro (on
closing the form or pressing a button) insert the row and put in the
necessary data( including calculating the index) and select the cell to the
right of the index.

I am not quite sure the "logic" that goes into your users selecting their
tract ID: do they know this from some other source or are they assigning it
as they go? If assigning, how do they avoid duplication (except maybe by
checking the list?) and wouldn't it be nicer to have Excel do these tasks for
them?

So for example, make a user form with 4 text boxes (representing the 4
segments of your tract ID). The user puts in the 1st 2 digits (in the first
2 boxes) and Excel finds the next available tract ID in that series and the
row where it should go. User enters any other needed data elements, presses
the button, and the new row is inserted and the data from the form copied in.
I would also suggest instead of a simple index number, why not a time/date
stamp? This takes no special procedure to calculate and, in sorted order,
performs the exact same function as your index would. Excel can get the time
from when the button was pressed and populate the "index" column.

If interested let me know and I will help work out the details - no time
now, go to run but will check back.

--
- K Dales


"Phil" wrote:

> Tom,
>
> Your solution worked perfectly. Thank you.
>
> However, after running it, I realized that the routine will not reveal the
> newest records (when I get the spreadsheet back from the field people), and
> therefore, I need to modify the requirement parameters.
>
> What I need instead, is after the user enters a new row for the record, the
> routine finds the last index number, then increment from the last number +1,
> and place it in the blank Index_No cell next to the new record.
>
> So, it would be like this:
>
> Index_No
> r2 1
> ...
> r526 525
>
> And if the user wanted to insert a new record at say, row 500, then the new
> Index_No for that record would be 526.
>
> That way, when I get the spreadsheet back, I can see instantly (after doing
> a sort) where all the new records are.
>
> Also, is there a way to incorporate the macro so that it will:
>
> a) do the insert row as well using maybe the "Selection.EntireRow.Insert"
> command, AND
>
> b) place the cursor in the 1st column to the right of the new index number?
>
> What do you think?
>
> Thanks again.
>
> Phil.
>
> "Tom Ogilvy" wrote:
>
> > Sub BBB()
> > Dim rng As Range
> > With ActiveSheet
> > Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
> > End With
> > Set rng = rng.Offset(0, -1)
> > rng(1) = 1
> > rng(2) = 2
> > rng(1).Resize(2, 1).AutoFill rng
> > End Sub
> >
> > Just run it after you have inserted your new row and have completed entering
> > your new data.
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> >
> >
> >
> > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> > > Hello,
> > >
> > > I am working with a group of individuals that will be passing around an
> > > excel spreadsheet to one another, and wanted to come up with a way to have
> > > the 1st column act as an index, with the key component requiring that the
> > > index column would automatically re-number itself, if someone entered a
> > new
> > > row.
> > >
> > > A typical value in the first column looks like this: 8-5-012-005
> > >
> > > Which in our case means that there are 4 series of number sets, separated
> > by
> > > dashes. So the first set is 8, the second 5, the third 012, and the fourth
> > > 005. The available range for the sets are 7 or 8 for the first, 5 through
> > 9
> > > for the second, and 0 through 130, and 0 through 200, respectively.
> > >
> > > So the user can pick any of these ranges for when they decide to create a
> > > new record (row).
> > >
> > > Here is the way the spreadsheet columns currently look (always sorted by
> > > Tract_ID):
> > >
> > > Row-1 Tract_ID Parcel_ID
> > > Row-2 7-5-065-105 01245787
> > > Row-3 7-5-112-005 01245787
> > > Row-4 8-5-012-005 01245787
> > > Row-5 8-6-030-125 01245787
> > >
> > > Now, here is the way I'd like to have the spreadsheet columns look with
> > the
> > > Index_No (can be either Numeric or Text - depending on your
> > recommendations).
> > > The sort order is based on 1st, the index number, then 2nd the Tract_ID:
> > >
> > > Row-1 Index_No Tract_ID Parcel_ID
> > > Row-2 1 7-5-065-105 01245787
> > > Row-3 2 7-5-112-005 0126A560
> > > Row-4 3 8-5-012-005 01005147
> > > Row-5 4 8-6-030-125 01000541
> > >
> > > Then, let's say the user wants to enter a new value like say, 7-5-105-021.
> > > That value would need to go between Row-1 and Row-2, which, if they just
> > > inserted the value in the row of their choice, would screw up the
> > indexing.
> > >
> > > What I need is a way to ALWAYS create an index (automatically), no matter
> > > where they decide to put the value in the spreadsheet, AND it would update
> > > all of the other Indexes as well (very important requirement).
> > >
> > > So the end result would be this:
> > >
> > > Row-1 Index_No Tract_ID Parcel_ID
> > > Row-2 1 7-5-065-105 01245787
> > > Row-3 2 7-5-105-021 00547419
> > > Row-4 3 7-5-112-005 5126A560
> > > Row-5 4 8-5-012-005 00005147
> > > Row-6 5 8-6-030-125 00001541
> > >
> > > If you need more information, please let me know.
> > >
> > > TIA for your replies.
> > >
> > > Phil.
> > >
> > > BTW, if you are noticing that this was posted in another MS NG, you'd be
> > > correct, but I have decided to post here as well, as one of the
> > respondents
> > > commented that this might be better done with VBA.
> > >
> >
> >
> >

Hello,

A user form would be nice, but I don't have that kind of time. I wanted to
get something back to them by tomorrow. With regard to the time stamp, is
there a way to get that info from any current record? Or does it have to be
implemented after the fact? I just want to keep it simple right now. But
the form idea DOES have merits, and I DO want to look at it maybe later next
week.

Thanks for your reply.

Phil.

"K Dales" wrote:

> Phil:
> May I suggest a completely different approach?
>
> I think I would set up a userform for input, then would have the macro (on
> closing the form or pressing a button) insert the row and put in the
> necessary data( including calculating the index) and select the cell to the
> right of the index.
>
> I am not quite sure the "logic" that goes into your users selecting their
> tract ID: do they know this from some other source or are they assigning it
> as they go? If assigning, how do they avoid duplication (except maybe by
> checking the list?) and wouldn't it be nicer to have Excel do these tasks for
> them?
>
> So for example, make a user form with 4 text boxes (representing the 4
> segments of your tract ID). The user puts in the 1st 2 digits (in the first
> 2 boxes) and Excel finds the next available tract ID in that series and the
> row where it should go. User enters any other needed data elements, presses
> the button, and the new row is inserted and the data from the form copied in.
> I would also suggest instead of a simple index number, why not a time/date
> stamp? This takes no special procedure to calculate and, in sorted order,
> performs the exact same function as your index would. Excel can get the time
> from when the button was pressed and populate the "index" column.
>
> If interested let me know and I will help work out the details - no time
> now, go to run but will check back.
>
> --
> - K Dales
>
>
> "Phil" wrote:
>
> > Tom,
> >
> > Your solution worked perfectly. Thank you.
> >
> > However, after running it, I realized that the routine will not reveal the
> > newest records (when I get the spreadsheet back from the field people), and
> > therefore, I need to modify the requirement parameters.
> >
> > What I need instead, is after the user enters a new row for the record, the
> > routine finds the last index number, then increment from the last number +1,
> > and place it in the blank Index_No cell next to the new record.
> >
> > So, it would be like this:
> >
> > Index_No
> > r2 1
> > ...
> > r526 525
> >
> > And if the user wanted to insert a new record at say, row 500, then the new
> > Index_No for that record would be 526.
> >
> > That way, when I get the spreadsheet back, I can see instantly (after doing
> > a sort) where all the new records are.
> >
> > Also, is there a way to incorporate the macro so that it will:
> >
> > a) do the insert row as well using maybe the "Selection.EntireRow.Insert"
> > command, AND
> >
> > b) place the cursor in the 1st column to the right of the new index number?
> >
> > What do you think?
> >
> > Thanks again.
> >
> > Phil.
> >
> > "Tom Ogilvy" wrote:
> >
> > > Sub BBB()
> > > Dim rng As Range
> > > With ActiveSheet
> > > Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
> > > End With
> > > Set rng = rng.Offset(0, -1)
> > > rng(1) = 1
> > > rng(2) = 2
> > > rng(1).Resize(2, 1).AutoFill rng
> > > End Sub
> > >
> > > Just run it after you have inserted your new row and have completed entering
> > > your new data.
> > >
> > > --
> > > Regards,
> > > Tom Ogilvy
> > >
> > >
> > >
> > >
> > > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > > news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> > > > Hello,
> > > >
> > > > I am working with a group of individuals that will be passing around an
> > > > excel spreadsheet to one another, and wanted to come up with a way to have
> > > > the 1st column act as an index, with the key component requiring that the
> > > > index column would automatically re-number itself, if someone entered a
> > > new
> > > > row.
> > > >
> > > > A typical value in the first column looks like this: 8-5-012-005
> > > >
> > > > Which in our case means that there are 4 series of number sets, separated
> > > by
> > > > dashes. So the first set is 8, the second 5, the third 012, and the fourth
> > > > 005. The available range for the sets are 7 or 8 for the first, 5 through
> > > 9
> > > > for the second, and 0 through 130, and 0 through 200, respectively.
> > > >
> > > > So the user can pick any of these ranges for when they decide to create a
> > > > new record (row).
> > > >
> > > > Here is the way the spreadsheet columns currently look (always sorted by
> > > > Tract_ID):
> > > >
> > > > Row-1 Tract_ID Parcel_ID
> > > > Row-2 7-5-065-105 01245787
> > > > Row-3 7-5-112-005 01245787
> > > > Row-4 8-5-012-005 01245787
> > > > Row-5 8-6-030-125 01245787
> > > >
> > > > Now, here is the way I'd like to have the spreadsheet columns look with
> > > the
> > > > Index_No (can be either Numeric or Text - depending on your
> > > recommendations).
> > > > The sort order is based on 1st, the index number, then 2nd the Tract_ID:
> > > >
> > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > Row-2 1 7-5-065-105 01245787
> > > > Row-3 2 7-5-112-005 0126A560
> > > > Row-4 3 8-5-012-005 01005147
> > > > Row-5 4 8-6-030-125 01000541
> > > >
> > > > Then, let's say the user wants to enter a new value like say, 7-5-105-021.
> > > > That value would need to go between Row-1 and Row-2, which, if they just
> > > > inserted the value in the row of their choice, would screw up the
> > > indexing.
> > > >
> > > > What I need is a way to ALWAYS create an index (automatically), no matter
> > > > where they decide to put the value in the spreadsheet, AND it would update
> > > > all of the other Indexes as well (very important requirement).
> > > >
> > > > So the end result would be this:
> > > >
> > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > Row-2 1 7-5-065-105 01245787
> > > > Row-3 2 7-5-105-021 00547419
> > > > Row-4 3 7-5-112-005 5126A560
> > > > Row-5 4 8-5-012-005 00005147
> > > > Row-6 5 8-6-030-125 00001541
> > > >
> > > > If you need more information, please let me know.
> > > >
> > > > TIA for your replies.
> > > >
> > > > Phil.
> > > >
> > > > BTW, if you are noticing that this was posted in another MS NG, you'd be
> > > > correct, but I have decided to post here as well, as one of the
> > > respondents
> > > > commented that this might be better done with VBA.
> > > >
> > >
> > >
> > >

So you want this for the user.

Assuming the user has selected a cell or the row where they want to enter
the data, then they run the code.

Sub InsertIndex()
ActiveCell.EntireRow.Insert
lastrow = cells(rows.count,1).End(xlup).row
Cells(ActiveCell.Row,1).Value = cells(lastrow,1).Value + 1
cells(ActiveCell.row,2).Select
End Sub

I assume the last number is in the last row - since you were showing index
numbers like r1 and r525, I assume they are not numbers.

If they were numbers you could do
Cells(ActiveCell.Row,1).Value = Application.Max(columns(1))+1

If you want the macro to find the appropriate row based on the user
providing the new number information, then you might as well go with the
Userform approach.

--
Regards,
Tom Ogilvy



"Phil" <Phil@discussions.microsoft.com> wrote in message
news:1816DD18-0D08-4444-92AF-D247BDE23EC7@microsoft.com...
> Hello,
>
> A user form would be nice, but I don't have that kind of time. I wanted to
> get something back to them by tomorrow. With regard to the time stamp, is
> there a way to get that info from any current record? Or does it have to
be
> implemented after the fact? I just want to keep it simple right now. But
> the form idea DOES have merits, and I DO want to look at it maybe later
next
> week.
>
> Thanks for your reply.
>
> Phil.
>
> "K Dales" wrote:
>
> > Phil:
> > May I suggest a completely different approach?
> >
> > I think I would set up a userform for input, then would have the macro
(on
> > closing the form or pressing a button) insert the row and put in the
> > necessary data( including calculating the index) and select the cell to
the
> > right of the index.
> >
> > I am not quite sure the "logic" that goes into your users selecting
their
> > tract ID: do they know this from some other source or are they assigning
it
> > as they go? If assigning, how do they avoid duplication (except maybe
by
> > checking the list?) and wouldn't it be nicer to have Excel do these
tasks for
> > them?
> >
> > So for example, make a user form with 4 text boxes (representing the 4
> > segments of your tract ID). The user puts in the 1st 2 digits (in the
first
> > 2 boxes) and Excel finds the next available tract ID in that series and
the
> > row where it should go. User enters any other needed data elements,
presses
> > the button, and the new row is inserted and the data from the form
copied in.
> > I would also suggest instead of a simple index number, why not a
time/date
> > stamp? This takes no special procedure to calculate and, in sorted
order,
> > performs the exact same function as your index would. Excel can get the
time
> > from when the button was pressed and populate the "index" column.
> >
> > If interested let me know and I will help work out the details - no time
> > now, go to run but will check back.
> >
> > --
> > - K Dales
> >
> >
> > "Phil" wrote:
> >
> > > Tom,
> > >
> > > Your solution worked perfectly. Thank you.
> > >
> > > However, after running it, I realized that the routine will not reveal
the
> > > newest records (when I get the spreadsheet back from the field
people), and
> > > therefore, I need to modify the requirement parameters.
> > >
> > > What I need instead, is after the user enters a new row for the
record, the
> > > routine finds the last index number, then increment from the last
number +1,
> > > and place it in the blank Index_No cell next to the new record.
> > >
> > > So, it would be like this:
> > >
> > > Index_No
> > > r2 1
> > > ...
> > > r526 525
> > >
> > > And if the user wanted to insert a new record at say, row 500, then
the new
> > > Index_No for that record would be 526.
> > >
> > > That way, when I get the spreadsheet back, I can see instantly (after
doing
> > > a sort) where all the new records are.
> > >
> > > Also, is there a way to incorporate the macro so that it will:
> > >
> > > a) do the insert row as well using maybe the
"Selection.EntireRow.Insert"
> > > command, AND
> > >
> > > b) place the cursor in the 1st column to the right of the new index
number?
> > >
> > > What do you think?
> > >
> > > Thanks again.
> > >
> > > Phil.
> > >
> > > "Tom Ogilvy" wrote:
> > >
> > > > Sub BBB()
> > > > Dim rng As Range
> > > > With ActiveSheet
> > > > Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
> > > > End With
> > > > Set rng = rng.Offset(0, -1)
> > > > rng(1) = 1
> > > > rng(2) = 2
> > > > rng(1).Resize(2, 1).AutoFill rng
> > > > End Sub
> > > >
> > > > Just run it after you have inserted your new row and have completed
entering
> > > > your new data.
> > > >
> > > > --
> > > > Regards,
> > > > Tom Ogilvy
> > > >
> > > >
> > > >
> > > >
> > > > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > > > news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> > > > > Hello,
> > > > >
> > > > > I am working with a group of individuals that will be passing
around an
> > > > > excel spreadsheet to one another, and wanted to come up with a way
to have
> > > > > the 1st column act as an index, with the key component requiring
that the
> > > > > index column would automatically re-number itself, if someone
entered a
> > > > new
> > > > > row.
> > > > >
> > > > > A typical value in the first column looks like this: 8-5-012-005
> > > > >
> > > > > Which in our case means that there are 4 series of number sets,
separated
> > > > by
> > > > > dashes. So the first set is 8, the second 5, the third 012, and
the fourth
> > > > > 005. The available range for the sets are 7 or 8 for the first, 5
through
> > > > 9
> > > > > for the second, and 0 through 130, and 0 through 200,
respectively.
> > > > >
> > > > > So the user can pick any of these ranges for when they decide to
create a
> > > > > new record (row).
> > > > >
> > > > > Here is the way the spreadsheet columns currently look (always
sorted by
> > > > > Tract_ID):
> > > > >
> > > > > Row-1 Tract_ID Parcel_ID
> > > > > Row-2 7-5-065-105 01245787
> > > > > Row-3 7-5-112-005 01245787
> > > > > Row-4 8-5-012-005 01245787
> > > > > Row-5 8-6-030-125 01245787
> > > > >
> > > > > Now, here is the way I'd like to have the spreadsheet columns
look with
> > > > the
> > > > > Index_No (can be either Numeric or Text - depending on your
> > > > recommendations).
> > > > > The sort order is based on 1st, the index number, then 2nd the
Tract_ID:
> > > > >
> > > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > > Row-2 1 7-5-065-105 01245787
> > > > > Row-3 2 7-5-112-005 0126A560
> > > > > Row-4 3 8-5-012-005 01005147
> > > > > Row-5 4 8-6-030-125 01000541
> > > > >
> > > > > Then, let's say the user wants to enter a new value like say,
7-5-105-021.
> > > > > That value would need to go between Row-1 and Row-2, which, if
they just
> > > > > inserted the value in the row of their choice, would screw up the
> > > > indexing.
> > > > >
> > > > > What I need is a way to ALWAYS create an index (automatically),
no matter
> > > > > where they decide to put the value in the spreadsheet, AND it
would update
> > > > > all of the other Indexes as well (very important requirement).
> > > > >
> > > > > So the end result would be this:
> > > > >
> > > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > > Row-2 1 7-5-065-105 01245787
> > > > > Row-3 2 7-5-105-021 00547419
> > > > > Row-4 3 7-5-112-005 5126A560
> > > > > Row-5 4 8-5-012-005 00005147
> > > > > Row-6 5 8-6-030-125 00001541
> > > > >
> > > > > If you need more information, please let me know.
> > > > >
> > > > > TIA for your replies.
> > > > >
> > > > > Phil.
> > > > >
> > > > > BTW, if you are noticing that this was posted in another MS NG,
you'd be
> > > > > correct, but I have decided to post here as well, as one of the
> > > > respondents
> > > > > commented that this might be better done with VBA.
> > > > >
> > > >
> > > >
> > > >

Hi Tom,

How much trouble is it to create an "Undo" option? I tried to undo an entry
(testing), and it would not allow it. What do you recomend?

Thanks.

Phil.

"Tom Ogilvy" wrote:

> So you want this for the user.
>
> Assuming the user has selected a cell or the row where they want to enter
> the data, then they run the code.
>
> Sub InsertIndex()
> ActiveCell.EntireRow.Insert
> lastrow = cells(rows.count,1).End(xlup).row
> Cells(ActiveCell.Row,1).Value = cells(lastrow,1).Value + 1
> cells(ActiveCell.row,2).Select
> End Sub
>
> I assume the last number is in the last row - since you were showing index
> numbers like r1 and r525, I assume they are not numbers.
>
> If they were numbers you could do
> Cells(ActiveCell.Row,1).Value = Application.Max(columns(1))+1
>
> If you want the macro to find the appropriate row based on the user
> providing the new number information, then you might as well go with the
> Userform approach.
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Phil" <Phil@discussions.microsoft.com> wrote in message
> news:1816DD18-0D08-4444-92AF-D247BDE23EC7@microsoft.com...
> > Hello,
> >
> > A user form would be nice, but I don't have that kind of time. I wanted to
> > get something back to them by tomorrow. With regard to the time stamp, is
> > there a way to get that info from any current record? Or does it have to
> be
> > implemented after the fact? I just want to keep it simple right now. But
> > the form idea DOES have merits, and I DO want to look at it maybe later
> next
> > week.
> >
> > Thanks for your reply.
> >
> > Phil.
> >
> > "K Dales" wrote:
> >
> > > Phil:
> > > May I suggest a completely different approach?
> > >
> > > I think I would set up a userform for input, then would have the macro
> (on
> > > closing the form or pressing a button) insert the row and put in the
> > > necessary data( including calculating the index) and select the cell to
> the
> > > right of the index.
> > >
> > > I am not quite sure the "logic" that goes into your users selecting
> their
> > > tract ID: do they know this from some other source or are they assigning
> it
> > > as they go? If assigning, how do they avoid duplication (except maybe
> by
> > > checking the list?) and wouldn't it be nicer to have Excel do these
> tasks for
> > > them?
> > >
> > > So for example, make a user form with 4 text boxes (representing the 4
> > > segments of your tract ID). The user puts in the 1st 2 digits (in the
> first
> > > 2 boxes) and Excel finds the next available tract ID in that series and
> the
> > > row where it should go. User enters any other needed data elements,
> presses
> > > the button, and the new row is inserted and the data from the form
> copied in.
> > > I would also suggest instead of a simple index number, why not a
> time/date
> > > stamp? This takes no special procedure to calculate and, in sorted
> order,
> > > performs the exact same function as your index would. Excel can get the
> time
> > > from when the button was pressed and populate the "index" column.
> > >
> > > If interested let me know and I will help work out the details - no time
> > > now, go to run but will check back.
> > >
> > > --
> > > - K Dales
> > >
> > >
> > > "Phil" wrote:
> > >
> > > > Tom,
> > > >
> > > > Your solution worked perfectly. Thank you.
> > > >
> > > > However, after running it, I realized that the routine will not reveal
> the
> > > > newest records (when I get the spreadsheet back from the field
> people), and
> > > > therefore, I need to modify the requirement parameters.
> > > >
> > > > What I need instead, is after the user enters a new row for the
> record, the
> > > > routine finds the last index number, then increment from the last
> number +1,
> > > > and place it in the blank Index_No cell next to the new record.
> > > >
> > > > So, it would be like this:
> > > >
> > > > Index_No
> > > > r2 1
> > > > ...
> > > > r526 525
> > > >
> > > > And if the user wanted to insert a new record at say, row 500, then
> the new
> > > > Index_No for that record would be 526.
> > > >
> > > > That way, when I get the spreadsheet back, I can see instantly (after
> doing
> > > > a sort) where all the new records are.
> > > >
> > > > Also, is there a way to incorporate the macro so that it will:
> > > >
> > > > a) do the insert row as well using maybe the
> "Selection.EntireRow.Insert"
> > > > command, AND
> > > >
> > > > b) place the cursor in the 1st column to the right of the new index
> number?
> > > >
> > > > What do you think?
> > > >
> > > > Thanks again.
> > > >
> > > > Phil.
> > > >
> > > > "Tom Ogilvy" wrote:
> > > >
> > > > > Sub BBB()
> > > > > Dim rng As Range
> > > > > With ActiveSheet
> > > > > Set rng = .Range(.Cells(2, 2), .Cells(2, 2).End(xlDown))
> > > > > End With
> > > > > Set rng = rng.Offset(0, -1)
> > > > > rng(1) = 1
> > > > > rng(2) = 2
> > > > > rng(1).Resize(2, 1).AutoFill rng
> > > > > End Sub
> > > > >
> > > > > Just run it after you have inserted your new row and have completed
> entering
> > > > > your new data.
> > > > >
> > > > > --
> > > > > Regards,
> > > > > Tom Ogilvy
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Phil" <Phil@discussions.microsoft.com> wrote in message
> > > > > news:C7DAF639-3B30-44F4-8EA1-190F6B506AD1@microsoft.com...
> > > > > > Hello,
> > > > > >
> > > > > > I am working with a group of individuals that will be passing
> around an
> > > > > > excel spreadsheet to one another, and wanted to come up with a way
> to have
> > > > > > the 1st column act as an index, with the key component requiring
> that the
> > > > > > index column would automatically re-number itself, if someone
> entered a
> > > > > new
> > > > > > row.
> > > > > >
> > > > > > A typical value in the first column looks like this: 8-5-012-005
> > > > > >
> > > > > > Which in our case means that there are 4 series of number sets,
> separated
> > > > > by
> > > > > > dashes. So the first set is 8, the second 5, the third 012, and
> the fourth
> > > > > > 005. The available range for the sets are 7 or 8 for the first, 5
> through
> > > > > 9
> > > > > > for the second, and 0 through 130, and 0 through 200,
> respectively.
> > > > > >
> > > > > > So the user can pick any of these ranges for when they decide to
> create a
> > > > > > new record (row).
> > > > > >
> > > > > > Here is the way the spreadsheet columns currently look (always
> sorted by
> > > > > > Tract_ID):
> > > > > >
> > > > > > Row-1 Tract_ID Parcel_ID
> > > > > > Row-2 7-5-065-105 01245787
> > > > > > Row-3 7-5-112-005 01245787
> > > > > > Row-4 8-5-012-005 01245787
> > > > > > Row-5 8-6-030-125 01245787
> > > > > >
> > > > > > Now, here is the way I'd like to have the spreadsheet columns
> look with
> > > > > the
> > > > > > Index_No (can be either Numeric or Text - depending on your
> > > > > recommendations).
> > > > > > The sort order is based on 1st, the index number, then 2nd the
> Tract_ID:
> > > > > >
> > > > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > > > Row-2 1 7-5-065-105 01245787
> > > > > > Row-3 2 7-5-112-005 0126A560
> > > > > > Row-4 3 8-5-012-005 01005147
> > > > > > Row-5 4 8-6-030-125 01000541
> > > > > >
> > > > > > Then, let's say the user wants to enter a new value like say,
> 7-5-105-021.
> > > > > > That value would need to go between Row-1 and Row-2, which, if
> they just
> > > > > > inserted the value in the row of their choice, would screw up the
> > > > > indexing.
> > > > > >
> > > > > > What I need is a way to ALWAYS create an index (automatically),
> no matter
> > > > > > where they decide to put the value in the spreadsheet, AND it
> would update
> > > > > > all of the other Indexes as well (very important requirement).
> > > > > >
> > > > > > So the end result would be this:
> > > > > >
> > > > > > Row-1 Index_No Tract_ID Parcel_ID
> > > > > > Row-2 1 7-5-065-105 01245787
> > > > > > Row-3 2 7-5-105-021 00547419
> > > > > > Row-4 3 7-5-112-005 5126A560
> > > > > > Row-5 4 8-5-012-005 00005147
> > > > > > Row-6 5 8-6-030-125 00001541
> > > > > >
> > > > > > If you need more information, please let me know.
> > > > > >
> > > > > > TIA for your replies.
> > > > > >
> > > > > > Phil.
> > > > > >
> > > > > > BTW, if you are noticing that this was posted in another MS NG,
> you'd be
> > > > > > correct, but I have decided to post here as well, as one of the
> > > > > respondents
> > > > > > commented that this might be better done with VBA.
> > > > > >
> > > > >
> > > > >
> > > > >
>
>
>