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

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/

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

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/