Cronological sequence formula

[greasybob]

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

 

[Guest]

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

 

[Biff]

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

 

[Guest]

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.

 

[Biff]

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

 

[MyVeryOwnSelf]

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.