Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with this code
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
split post code (zip code) out of cell that includes full address | Excel Discussion (Misc queries) | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Shorten code to apply to all sheets except a few, instead of individually naming them, and later adding to code. | Excel Programming | |||
Protect Sheet with code, but then code will not Paste error. How do i get around this. Please read for explainations.... | Excel Programming | |||
Excel code convert to Access code - Concat & eliminate duplicates | Excel Programming |