Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a 2 column list with VBA using Worksheet change produces 1004 error
I have a 2 column range. In the the first column are job description
and in the second column the pay rate for that job description Occasionally, I need to add a job desciption at the bottom of the lis and I want the list to sort itself after I enter the new jo description. I also want the pay rates to sort with the jo descriptions at the same time. Below is the code I'm using. When I typ a new job description and hit ENTER the following error shows. Run-time error '1004': Application-defined or Object-defined error Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Columns(1, 2).Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub Many thanks Case -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a 2 column list with VBA using Worksheet change produces 1004 error
Try:
If Not Intersect(Target, Range("B:B")) Is Nothing Then Range("A:B").Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End If I've changed it so that it only sorts the list after you've entered the pay rate in the second column. If I've been overly helpful (! <g), change the If Not Intersect(Target, Range("B:B")) Is Nothing Then to If Not Intersect(Target, Range("A:B")) Is Nothing Then to catch changes in both columns A and B. -- HTH, Dianne Butterworth I have a 2 column range. In the the first column are job descriptions and in the second column the pay rate for that job description. Occasionally, I need to add a job desciption at the bottom of the list and I want the list to sort itself after I enter the new job description. I also want the pay rates to sort with the job descriptions at the same time. Below is the code I'm using. When I type a new job description and hit ENTER the following error shows. Run-time error '1004': Application-defined or Object-defined error Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) Columns(1, 2).Sort Key1:=Range("A1"), Order1:=xlAscending, _ Header:=xlGuess, OrderCustom:=1, _ MatchCase:=False, Orientation:=xlTopToBottom End Sub Many thanks Casey --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a 2 column list with VBA using Worksheet change produces 1004 error
Diane,
Thank you so much for your reply to my problem. It worked brilliantly And as to overstepping the question and including column B, it wa beyond my thinking, but a perfect addition. I am constantly amazed at the generousity of the people who frequen this forum. As a way of "giving back", I try to find problems listed i the forum which I feel confident to address, unfortunately there fe which I feel confident to help with at least that have not bee answered authoritively by others before me. But I will kee participating. And to you Diane, and all the wonderful Gurus of VBA Thank you. Case -- Message posted from http://www.ExcelForum.com |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sorting a 2 column list with VBA using Worksheet change produces 1004 error
Casey,
I hear ya. I've learned so much here and have tried out all sorts of new and exciting things - API calls, class modules, ADO. Fun stuff! When I see a chance to contribute, I try to. However I'm usually beaten to the punch by the mpep regulars. What a fantastic group! -- Dianne Butterworth Diane, Thank you so much for your reply to my problem. It worked brilliantly. And as to overstepping the question and including column B, it was beyond my thinking, but a perfect addition. I am constantly amazed at the generousity of the people who frequent this forum. As a way of "giving back", I try to find problems listed in the forum which I feel confident to address, unfortunately there few which I feel confident to help with at least that have not been answered authoritively by others before me. But I will keep participating. And to you Diane, and all the wonderful Gurus of VBA. Thank you. Casey --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro runtime 1004 error on opening worksheet | Excel Discussion (Misc queries) | |||
Error 1004 Copy method of worksheet class failed | Excel Discussion (Misc queries) | |||
Run-time error 1004: Cannot shift nonblank cells off the worksheet | Excel Discussion (Misc queries) | |||
RUNTIME ERROR '1004' --- Select method of worksheet class failed | Excel Programming | |||
Hyperlink written to worksheet produces error | Excel Programming |