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

[Gary''s Student]

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
>
>

[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

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>

[Gary''s Student]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >>
> >>
>
>
>

[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

"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> > 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
>> >>
>> >>
>>
>>
>>

[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.