Sorting a 2 column list with VBA using Worksheet change produces 1004 error

[Casey]

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

 

[Dianne Butterworth]

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.

 

[Casey]

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

 

[Dianne Butterworth]

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!