I got assistance for this code from this forum, however it is not working the
way I had planned. it creates the index numbers in column a, however it does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

I'd try this first:

Replace:
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
with
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes

(added double quotes around B1)

Mekinnik wrote:

I got assistance for this code from this forum, however it is not working the
way I had planned. it creates the index numbers in column a, however it does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

--

Dave Peterson

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

The sort statement is causing an error because the double quotes are missing

from:
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes

to:
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes


"Mekinnik" wrote:

I got assistance for this code from this forum, however it is not working the
way I had planned. it creates the index numbers in column a, however it does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

change this statement
from:
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
to:
Me.Cells(.Row, "A").Value = _
Me.Cells(worksheetfunction.max("A1:A500", "A").Value + 1


"Mekinnik" wrote:

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

I tried your change but it didn't sort anything at all, back to the drawing
board. Is what I am looking for is to bind column A to column b so that when
you sort column b it will keep keep the data together in each row

"Joel" wrote:

change this statement
from:
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
to:
Me.Cells(.Row, "A").Value = _
Me.Cells(worksheetfunction.max("A1:A500", "A").Value + 1


"Mekinnik" wrote:

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

My last change was to fix the problem with duplicate number in column A.
What the changge did was to look for the largest value in column A and placed
into column A one number larger then the max value.

The changge should not of effected the sorting.

"Mekinnik" wrote:

I tried your change but it didn't sort anything at all, back to the drawing
board. Is what I am looking for is to bind column A to column b so that when
you sort column b it will keep keep the data together in each row

"Joel" wrote:

change this statement
from:
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
to:
Me.Cells(.Row, "A").Value = _
Me.Cells(worksheetfunction.max("A1:A500", "A").Value + 1


"Mekinnik" wrote:

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

SOrry Joel,
Your change has done nothing I have tried everything from manipulating the
code to adding it to what I already have and everytime the line is place in
the code it stops assigning numbers and sorting. I cannot figure out what it
needs.

"Joel" wrote:

My last change was to fix the problem with duplicate number in column A.
What the changge did was to look for the largest value in column A and placed
into column A one number larger then the max value.

The changge should not of effected the sorting.

"Mekinnik" wrote:

I tried your change but it didn't sort anything at all, back to the drawing
board. Is what I am looking for is to bind column A to column b so that when
you sort column b it will keep keep the data together in each row

"Joel" wrote:

change this statement
from:
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
to:
Me.Cells(.Row, "A").Value = _
Me.Cells(worksheetfunction.max("A1:A500", "A").Value + 1


"Mekinnik" wrote:

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated

One more time. Tested this pretty good. i had some parethesis in the wrong
place.

Cells(.Row, "A").Value = _
WorksheetFunction.Max(Range("A1:A500")) + 1


"Mekinnik" wrote:

SOrry Joel,
Your change has done nothing I have tried everything from manipulating the
code to adding it to what I already have and everytime the line is place in
the code it stops assigning numbers and sorting. I cannot figure out what it
needs.

"Joel" wrote:

My last change was to fix the problem with duplicate number in column A.
What the changge did was to look for the largest value in column A and placed
into column A one number larger then the max value.

The changge should not of effected the sorting.

"Mekinnik" wrote:

I tried your change but it didn't sort anything at all, back to the drawing
board. Is what I am looking for is to bind column A to column b so that when
you sort column b it will keep keep the data together in each row

"Joel" wrote:

change this statement
from:
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
to:
Me.Cells(.Row, "A").Value = _
Me.Cells(worksheetfunction.max("A1:A500", "A").Value + 1


"Mekinnik" wrote:

Mr. Phillips,
Thank you for showing me the error I made, however another problem has
arisen. When it sorts column B it also sorts column A, which if just what I
wanted but when I enter in a new name it does not assign it the next highest
number in the sequence used it assigns it the next number based on the
previous cell number so I will end up with multible sequence numbers. How
would I remedy this problem?

"Bob Phillips" wrote:

You mis-copied it Mekinnik. The code that I gave you was

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range("B1"), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub


Note the quotes around B1 in the Sort statement row

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Mekinnik" wrote in message
...
I got assistance for this code from this forum, however it is not working
the
way I had planned. it creates the index numbers in column a, however it
does
not sort column b alphabetically keeping rows c-g with the same row when
sorted.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "B1:B500"

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "A").Value = Me.Cells(.Row - 1, "A").Value + 1
Me.Range("A:G").Sort key1:=Me.Range(B1), header:=xlYes
End With
End If


ws_exit:
Application.EnableEvents = True

End Sub

Any assistance would be appreciated