Cronological sequence formula
 

I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.
Thanks
Bobby


--
greasybob
------------------------------------------------------------------------
greasybob's Profile: http://www.excelforum.com/member.php...fo&userid=7923
View this thread: http://www.excelforum.com/showthread...hreadid=547626

Cronological sequence formula
 

Put the following in worksheet code:

Dim icount As Integer
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = icount
icount = icount + 1
Application.EnableEvents = True
End Sub
--
Gary''s Student


"greasybob" wrote:


I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.
Thanks
Bobby


--
greasybob
------------------------------------------------------------------------
greasybob's Profile: http://www.excelforum.com/member.php...fo&userid=7923
View this thread: http://www.excelforum.com/showthread...hreadid=547626

Cronological sequence formula
 

Hi GS!

If Intersect(Range("A1:A100"), Target) Is Nothing

Can you explain in logical terms what that line of code means?

What does Intersect mean?

Does Is Nothing mean if empty?

Biff

"Gary''s Student" wrote in message
...
Put the following in worksheet code:

Dim icount As Integer
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = icount
icount = icount + 1
Application.EnableEvents = True
End Sub
--
Gary''s Student


"greasybob" wrote:


I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.
Thanks
Bobby


--
greasybob
------------------------------------------------------------------------
greasybob's Profile:
http://www.excelforum.com/member.php...fo&userid=7923
View this thread:
http://www.excelforum.com/showthread...hreadid=547626

Cronological sequence formula
 

Hi Biff:

Just like Union is the combination of two ranges, Intersect is whats in
common in the two ranges. Target is the range of the "tickler" into the
event code. If Intersect is Nothing, that means that the changed cell is not
between A1 and A100 and the macro should quit.

This means that changing cells in other columns, even though the macro gets
entered each time, will not affect the worksheet.


This kind of code must be put in the worksheet code area, not a module.
--
Gary's Student


"Biff" wrote:

Hi GS!

If Intersect(Range("A1:A100"), Target) Is Nothing

Can you explain in logical terms what that line of code means?

What does Intersect mean?

Does Is Nothing mean if empty?

Biff

"Gary''s Student" wrote in message
...
Put the following in worksheet code:

Dim icount As Integer
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = icount
icount = icount + 1
Application.EnableEvents = True
End Sub
--
Gary''s Student


"greasybob" wrote:


I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.
Thanks
Bobby


--
greasybob
------------------------------------------------------------------------
greasybob's Profile:
http://www.excelforum.com/member.php...fo&userid=7923
View this thread:
http://www.excelforum.com/showthread...hreadid=547626

Cronological sequence formula
 

Got it! Thanks!

One of these days I'm gonna dive into VBA. I've got an autographed copy of
John Walkenbachs "Excel VBA Programming for Dummies" sittin on my desk. Just
need some motivation!

Biff

"Gary''s Student" wrote in message
...
Hi Biff:

Just like Union is the combination of two ranges, Intersect is whats in
common in the two ranges. Target is the range of the "tickler" into the
event code. If Intersect is Nothing, that means that the changed cell is
not
between A1 and A100 and the macro should quit.

This means that changing cells in other columns, even though the macro
gets
entered each time, will not affect the worksheet.


This kind of code must be put in the worksheet code area, not a module.
--
Gary's Student


"Biff" wrote:

Hi GS!

If Intersect(Range("A1:A100"), Target) Is Nothing

Can you explain in logical terms what that line of code means?

What does Intersect mean?

Does Is Nothing mean if empty?

Biff

"Gary''s Student" wrote in
message
...
Put the following in worksheet code:

Dim icount As Integer
Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("A1:A100"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
Target.Offset(0, 1).Value = icount
icount = icount + 1
Application.EnableEvents = True
End Sub
--
Gary''s Student


"greasybob" wrote:


I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.
Thanks
Bobby


--
greasybob
------------------------------------------------------------------------
greasybob's Profile:
http://www.excelforum.com/member.php...fo&userid=7923
View this thread:
http://www.excelforum.com/showthread...hreadid=547626

Cronological sequence formula
 

I'm needing a formula (may have to be VB) that will number my entries
based on the order that I put them in.
For instance; When I enter data somewhere in Col A, the adjacent cell
in Col B will show a "1" for my first entry then a "2" for my
second...and so on - as I work up and down the rows.

Here's one way.

First, open this dialog box:
Tools Options Calculation
and check the "Iteration" check box.

Then put this in B1:
=IF(A1="","",IF(B1="",MAX(B:B)+1,B1))

Then copy down B1 for as many rows as you need.