Capturing a Cell Reference After Update

[Ken]

With the following sample...

A B C
1. 2/1/05 AAA 344
2. 1/2/05 BBB 342
3.
4. 2/3/05 AAA 250

I want to create the following action in a macro.
When an entry is made in column B, I want the macro to enter the next
consecutive number higher than the maximum number in column C. I want that
new maximum number to be entered into column C next to the entry just made in
column B.
In the above example, if I enter anything into column B, the number 345
would be entered into C3.

1.Is the code below going in the right direction?
2. Instead of cell C2 getting the new max number, I need to "capture" the
row reference from the column B input and put the new max number into the
same row in column C.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Range("C2") = Application.WorksheetFunction.Max(Range("C:C")) + 1
End If
End Sub

TIA.

[Otto Moehrbach]

You almost have it. Change the "Range("C2")" to "Target.Offset(, 1)"
without the quotes.
HTH Otto
"Ken" <Ken@discussions.microsoft.com> wrote in message
news:BF311BA9-20C3-4596-8C32-A22880E61B8C@microsoft.com...
> With the following sample...
>
> A B C
> 1. 2/1/05 AAA 344
> 2. 1/2/05 BBB 342
> 3.
> 4. 2/3/05 AAA 250
>
> I want to create the following action in a macro.
> When an entry is made in column B, I want the macro to enter the next
> consecutive number higher than the maximum number in column C. I want that
> new maximum number to be entered into column C next to the entry just made
> in
> column B.
> In the above example, if I enter anything into column B, the number 345
> would be entered into C3.
>
> 1.Is the code below going in the right direction?
> 2. Instead of cell C2 getting the new max number, I need to "capture" the
> row reference from the column B input and put the new max number into the
> same row in column C.
>
> Option Explicit
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Not Intersect(Target, Range("B:B")) Is Nothing Then
> Range("C2") = Application.WorksheetFunction.Max(Range("C:C")) + 1
> End If
> End Sub
>
> TIA.

[Ken]

Thank you, Otto.

"Otto Moehrbach" wrote:

> You almost have it. Change the "Range("C2")" to "Target.Offset(, 1)"
> without the quotes.
> HTH Otto
> "Ken" <Ken@discussions.microsoft.com> wrote in message
> news:BF311BA9-20C3-4596-8C32-A22880E61B8C@microsoft.com...
> > With the following sample...
> >
> > A B C
> > 1. 2/1/05 AAA 344
> > 2. 1/2/05 BBB 342
> > 3.
> > 4. 2/3/05 AAA 250
> >
> > I want to create the following action in a macro.
> > When an entry is made in column B, I want the macro to enter the next
> > consecutive number higher than the maximum number in column C. I want that
> > new maximum number to be entered into column C next to the entry just made
> > in
> > column B.
> > In the above example, if I enter anything into column B, the number 345
> > would be entered into C3.
> >
> > 1.Is the code below going in the right direction?
> > 2. Instead of cell C2 getting the new max number, I need to "capture" the
> > row reference from the column B input and put the new max number into the
> > same row in column C.
> >
> > Option Explicit
> > Private Sub Worksheet_Change(ByVal Target As Range)
> > If Not Intersect(Target, Range("B:B")) Is Nothing Then
> > Range("C2") = Application.WorksheetFunction.Max(Range("C:C")) + 1
> > End If
> > End Sub
> >
> > TIA.
>
>
>