Solved

Two Excel questions

Posted on 2001-09-10
6
150 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 50 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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

759 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now