Help with this code

[Mekinnik]

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]

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)

 

[Bob Phillips]

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)

 

[Joel]

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]

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?

 

[Joel]

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]

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]

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]

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]

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