Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
assign formula to another cell | Excel Worksheet Functions | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
Creating a check mark box | Setting up and Configuration of Excel |