?
Solved

Two Excel questions

Posted on 2001-09-10
6
Medium Priority
?
163 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
  • 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
Independent Software Vendors: 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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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
Course of the Month17 days, 1 hour left to enroll

862 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