Help on Excel Worksheet Change Macro

[Enuflogic]

New to the forums, please be easy

I have an Excel worksheet change macro set up for data entered in a specific column to auto sort at a couple of different locations in the sreadsheet. It's set up like this:

CONCRETE
Name | Data (this cell has a named reference of CONCSTART to indicate start for sort function)
Name | Data
Name | Data

MASONRY
Name | Data (this cell has a named reference of MASSTART to indicate start for sort function)
Name | Data
Name | Data

When data is entered into the data column, it auto sorts ascending. I've used this before, and have set it up again, but here's my issue. My sections sort independently of each other, and I have about 10 or so. Some of the sections sort fine, others wont sort, but I'm not getting a VBA error, and all my scripts match syntax wise.

Here is the code I'm using:

Private Sub Worksheet_Change(ByVal Target As Range)
'Check that cell changed was in col H. If not, exit.
If Target.Column <> 8 Then Exit Sub

'Sorts Concrete cells ascending by bid amount
Range("CONCRETE").Sort Key1:=Range("CONSTART"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

'Sorts Masonry cells ascending by bid amount
Range("MASONRY").Sort Key1:=Range("MASSTART"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Any thoughts?

I'm using Excel 2000, and the worksheet I am modifying was created in 2007.

 

[p45cal]

Probably because the ranges "CONCRETE" and "MASONRY" do not cover the whole range of cells to be sorted. They're not just single columns are they?

In the immediate pane of the VBE, try
Range("CONCRETE").Select
and
Range("MASONRY").Select

Do this with your original (working) workbook and see if they are more or less the same.

 

[Enuflogic]

"CONCRETE" and "MASONRY" are named ranges that encompass about 3 different rows. I've double checked the named range(s), and it references the cells they are supposed to

 

[p45cal]

Can you post a version of the workbook on the interweb somewhere (box.net etc. perhpas?)?

 

[Enuflogic]

I noticed this also:

My named range encompasses 3 rows, 2 of which contain data. "CONCRETE" is working fine, "MASONRY" is not, however, If I insert a row between my 2 that contain data, and place data in this new row, my sort will now function properly from this inserted line and the data line that was moved down a row, even though my "MASSTART" (which is where it should start sorting from) stayed in the same location.

 

[Enuflogic]

Can you post a version of the workbook on the interweb somewhere (box.net etc. perhpas?)?

http://www.box.net/shared/h7ckb6f46y

 

[p45cal]

Maybe change
Header:= xlGuess to xlYes or xlNo depending…

 

[Enuflogic]

Can you post a version of the workbook on the interweb somewhere (box.net etc. perhpas?)?

Someone DL'd it, I'm guessing it was you.

If you go to "Concrete" and put the following numbers in bid amount, you will see how it is supposed to work.

$15,000
$20,000
$10,000

You will see that it autosorts and placed the entire line with the $10,000 first. If you go to the next section down, "Masonry" you will see that it doesn't do this. Most of the sections are working, there are 5 total that are not: Masonry, Partitions, Drywall, Doors, Windows.

 

[p45cal]

Maybe change
Header:= xlGuess to xlYes or xlNo depending…

xlNo seems to sort it.

 

[Enuflogic]

xlNo seems to sort it.

I changed that and it works! Any idea why? What does that change reference? I got this formula about 2 years ago and forgot half of what went into it.

THANKS by the way!