Try:
If Intersect(.Cells, Me.Range("B1:B10")) = "" Then Exit
Sub
(one line)
underneath the 1st intersect line.
Jason
-----Original Message-----
Jason,
Thanks for the prompt response! This worked great after
I figured out that
the "Exit Sub" was not on the same line as the Then
statement.
I also changed the RngAbov to "A:A" so as to include all
rows. I have some
items that are seperate from others.
The only other thing I noticed is that if I hit delete in
a B cell that has
no inventory number it still creates one. I can live
with it, but if you
have a quick fix I'd love to have it.
Thanks again.
"Jason Morin" wrote:
Click on the worksheet, right-click and select View
Code,
and paste in the macro below. It triggers off an entry
into B column:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim RngAbov As Range
Dim MaxVal As Variant
With Target
If .Cells.Count 1 Then Exit Sub
If Intersect(.Cells, Me.Range("B:B")) Is Nothing
Then
Exit Sub
Set RngAbov = Me.Range("a1", .Offset(-1, 0))
MaxVal = Application.Max(RngAbov)
Application.EnableEvents = False
.Offset(0, -1).Value = MaxVal + 1
.Offset(1, 0).Select
Application.EnableEvents = True
End With
End Sub
----
HTH
Jason
Atlanta, GA
-----Original Message-----
I'm trying to create a spreadsheet that will
automatically input a new item
number when I enter a new item on a list. Easy
enough,
but the catch is I
want the item number to remain assoicated with the
item
regardless of sorting.
In the classic Excel Help file manner I will explain
what
I want.
Current on screen data
A\B\C
1\Oranges\52
2\Apples\34
3\Bananas\97
If I sort by column B:
A\B\C
2\Apples\34
3\Bananas\97
1\Oranges\52
Now I add another item (cherries\63) to the next
available row (B5). I want
my output to look like this: (Note the number 4 is
automated)
A\B\C
2\Apples\34
3\Bananas\97
1\Oranges\52
4\Cherries\63
Any ideas? I've seen lots of consecutive number
ideas,
but never one like
this. Please help.
.
.
|