VBA Sort when Column Order Changes

[cashcraft]

Challenge!

I have a 4-item sort that relies on my fields being in a static order. I want the ability to rearrange my columns without having to revise my code, so I need the Range.Sort to look up the column location for each of my fields. Are you clever enough to figure this one out? Is the key a dynamic lookup based on column name?

Assume header row 1 is as follows:
C = Work Order
E = Group
AN = Due Date
V = Site

Please Login or Register  to view this content.

To throw a wrench in things, my actual column headings have two carriage returns after each field name so "Work Order"&Char(10)&Char(10) in code terms.

Many Thanks!

[bsalv]

4 columns to sort ?

Please Login or Register  to view this content.

[cashcraft]

Thanks for the reply, bsalv! I gave this a try after changing "MySheet" to the correct sheet name and changing Range to my named range that begins in A1 (I also tried leaving it as A1). In all cases I keep getting the Else clause Msgbox. I narrowed it down one item using language like this for each K item 1-4.

Please Login or Register  to view this content.

In each case, I get the "not ok" message. I also tried removing the char(10) and changing my "Site" & Chr(10) & Chr(10) to just "Site" in box code and in Row 1 of my worksheet. I tried to look up Application.Index but couldn't find anything excep Application.WorksheetFunction.Index however when I revised to that I got a run time error 13 type mismatch message. Are we trying to count text with the IsNumeric If statement? Where should I go from here?

[bsalv]

can you post an example of your file, so that's easier for me to see how your "Site..." is exactly.
Let's say the header and 5 lines of (dummy) data.

[cashcraft]

Example Dynamic Column Sort VBA.xlsm

Here is a sample spreadsheet where I copied the header row directly from my original file. Thanks!