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

C

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
 
D

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.
 
C

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
 
D

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!
 
Top