Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Two Excel questions

Posted on 2001-09-10
6
Medium Priority
?
161 Views
Last Modified: 2008-02-01
Basics:  VB6 sp5, Windows/NT, Excel 2000

In my VB application I create and populate an Excel worksheet successfully.  Now I want to do two additional things to the spreadsheet as follows:

1.  How do I sort the spreadsheet based on two columns?  Column A should be ascending and Column B descending.  However, the sort should start with row 3 as rows 1 and 2 are column headers and thus should not be sorted.

2.  How do I right justify a certain cell in the spreadsheet?

Thanks for the help.
0
Comment
Question by:EYoung
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 12

Accepted Solution

by:
roverm earned 200 total points
ID: 6472349
This is the code for sorting:

Sub Sortit()
Dim oSheet As Worksheet
Dim oCurSheet As Worksheet

    'first move first 2 rows to temporary sheet:
    Set oCurSheet = ActiveSheet
    Rows("1:2").Copy
    Set oSheet = ActiveWorkbook.Sheets.Add
    oSheet.Activate
    Rows("1:2").Select
    ActiveSheet.Paste
   
    'then delete the first rows
    oCurSheet.Activate
    Rows("1:2").Delete Shift:=xlUp
   
    'sort it
    Cells.Sort Key1:=Range("A1"), Order1:=xlAscending, Key2:=Range("B1") _
        , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom
       
    'get the first rows back
    oSheet.Activate
    Rows("1:2").Copy
    oCurSheet.Activate
    Rows("1:1").Insert Shift:=xlDown
   
    'now delete temp sheet
    Application.DisplayAlerts = False
    oSheet.Delete
    Application.DisplayAlerts = True
   
    Application.CutCopyMode = False
   
    Set oSheet = Nothing
    Set oCurSheet = Nothing
   
End Sub

And this for the alignment of a cell:

cells(1,1).HorizontalAlignment = xlRight

D'Mzzl!
RoverM
0
 
LVL 12

Expert Comment

by:roverm
ID: 6472356
Of course, above code was written in VBA (Excel) just alter it to run from VB or place the code in the Excel workbook and run it as an macro (Application.Run "Sortit").

D'Mzzl!
RoverM
0
 
LVL 7

Author Comment

by:EYoung
ID: 6472467
roverm,

I tried to sort the spreadsheet using the following code but it does not appear to be working.  Do I need all of the parameters, i.e. Header, OrderCustom, Orientation, etc.?  (For testing purposes I am including the first two rows in the sort just to see how it works.)

    W.Cells.Sort Key1:=Range("C1"), Order1:=xlAscending, Key2:=Range("E1") _
       , Order2:=xlDescending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
       False, Orientation:=xlTopToBottom


Thanks
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 7

Author Comment

by:EYoung
ID: 6472478
Do I need to specify the rows to sort or expand the Range option?  (Does Range("C1") mean sort all of column C?)
0
 
LVL 7

Author Comment

by:EYoung
ID: 6472554
Thanks.  It works just fine.
0
 
LVL 12

Expert Comment

by:roverm
ID: 6473267
Your first comment:
No, you don't need them all, every prop got it's own default. Like Orientation = xlTopBottom.
Just try to leave one out and see ;-)

Second comment:
Yes, Range("C1") means ON what column to sort. To select a range:
Columns("A1:E1").Select
Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Last comment:
Thanks for the points !

Glad I could help.

D'Mzzl!
RoverM
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question