×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Script to sort part numbers

Script to sort part numbers

Script to sort part numbers

(OP)
I'm trying to run a script from a CAD software which exports property values into an Excel spreadsheet. I need to then sort the spreadsheet by part numbers (which are located in column G) but my script is not working correctly. My data range is A1:G50. Any ideas on why this isn't working?

Const xlCenter = -4108
Const xlAscending = 1
Const xlYes = 1
Const xlSortOnValues = 0
Const xlSortNormal = 0
Const xlTopToBottom = 1
Const xlPinYin = 1

Set objRange = myworksheet.UsedRange
Set objRange2 = Excel.Range("G1")
objRange.Sort objRange2, xlAscending, , , , , , xlYes
 

http://excelspreadsheetshelp.blogspot.com

RE: Script to sort part numbers

CODE

Set objRange2 = Excel.Range("G1")

"G1" is a single cell.

When you sort a single cell nothing useful happens.

RE: Script to sort part numbers

Unless you want to sort its DNA.

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Script to sort part numbers

(OP)
I figured it out:

Excel.Range("A:G").Select
Excel.Selection.Sort Excel.Range("G1"),1,Excel.Range("A1"),,1,Excel.Range("B1"),1,1,1,False

You first specify the data range then the single G1, A1, B1 tell it which column to sort by.

http://excelspreadsheetshelp.blogspot.com

RE: Script to sort part numbers

why cant you just use a pivot table? Usually this type of list consists of a umber of idetical items where you need to count them. A pivot table is excellent for this.

Best regards

Morten

RE: Script to sort part numbers

my guess is he wants the code to do it automatically

Only put off until tomorrow what you are willing to die having left undone. - Pablo Picasso

RE: Script to sort part numbers

Sort on Column G will render the same result.  

I use to add macros and scripts to excel files, but when you do they end up getting caught up in security warning etc.  Hence I have moved away from including these, and gone back to the old fashion way.   

RE: Script to sort part numbers

Quote:

I use to add macros and scripts to excel files, but when you do they end up getting caught up in security warning etc.  Hence I have moved away from including these, and gone back to the old fashion way.   

Is it really so hard to get people to click on the "Enable Content" button?  Or better get them to set up a "trusted" folder, and copy spreadsheets with macros there.

To just stop using macros because of the security warnings strikes me as anti-productive.

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/
 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources