Questions

Excel calculations

Tags:
+
0 Votes
Locked

Excel calculations

murgash
Hello,
i have a spreadsheet with 1124 rows and would like to write a macro to sort ascending every even row. For example after running the macro i want it to sort row2, then jump to and sort row4, row6 and so on..
I have digged a macro from your forum which works really nice except it sorts EVERY row in a range.
Any help will be greatly appreciated!
Here is the macro which you can revise:

Sub Sort_Test()
Dim RgToSort As Range
Dim RgRow As Range
Dim x As Long
Set RgToSort = Range(Range("A1:AE1124"), Range("A1:AE1124").End(xlDown))
For Each RgRow In RgToSort.Rows
RgRow.Sort Key1:=Range(RgRow.Item(1).Address), Order1:=xlAscending, Orientation:=xlLeftToRight, OrderCustom:=1
Next
End Sub

Btw, i am using excel 2007 if it makes any difference.
Thanks in advance for every help.
  • +
    0 Votes

    In Excel, the Lookup function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the Lookup function:
    Syntax #1

    In Syntax #1, the Lookup function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

    The syntax for the Lookup function is:

    Lookup( value, lookup_range, result_range )

    value is the value to search for in the lookup_range.

    lookup_range is a single row or single column of data that is sorted in ascending order. The Lookup function searches for value in this range.

    result_range is a single row or single column of data that is the same size as the lookup_range. The Lookup function searches for the value in the lookup_range and returns the value from the same position in the result_range.

    Note:

    If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

    If the value is smaller than all of the values in the lookup_range, then the Lookup function will return #N/A.

    If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value.

    To sort the sheets in ascending order:

    1. Press Alt+F11, and then select a VBAProject (for example, press VBAProject (Financial Statements.xls)).
    2. From the Insert menu, select Module.
    3. In the Module, type the code lines displayed in the SheetInABC_Order macro in the screenshot.
    4. Operate the macro from the Module by pressing F5.
    OR
    Press Alt+F11 and return to Excel, assign the macro to any Excel object as a button or icon, or press Alt+F8 to select the macro, and then click Run.

    Please post back if you have any more problems or questions.

    +
    0 Votes
    ThumbsUp2

    You can't sort just one row. You would need to copy each of the even numbered rows to a new sheet, sort them there, then use the contents of that 2nd sheet to replace the even rows on the first sheet.

  • +
    0 Votes

    In Excel, the Lookup function returns a value from a range (one row or one column) or from an array. There are 2 different syntaxes for the Lookup function:
    Syntax #1

    In Syntax #1, the Lookup function searches for value in the lookup_range and returns the value in the result_range that is in the same position.

    The syntax for the Lookup function is:

    Lookup( value, lookup_range, result_range )

    value is the value to search for in the lookup_range.

    lookup_range is a single row or single column of data that is sorted in ascending order. The Lookup function searches for value in this range.

    result_range is a single row or single column of data that is the same size as the lookup_range. The Lookup function searches for the value in the lookup_range and returns the value from the same position in the result_range.

    Note:

    If the Lookup function can not find an exact match, it chooses the largest value in the lookup_range that is less than or equal to the value.

    If the value is smaller than all of the values in the lookup_range, then the Lookup function will return #N/A.

    If the values in the Lookup_range are not sorted in ascending order, the Lookup function will return the incorrect value.

    To sort the sheets in ascending order:

    1. Press Alt+F11, and then select a VBAProject (for example, press VBAProject (Financial Statements.xls)).
    2. From the Insert menu, select Module.
    3. In the Module, type the code lines displayed in the SheetInABC_Order macro in the screenshot.
    4. Operate the macro from the Module by pressing F5.
    OR
    Press Alt+F11 and return to Excel, assign the macro to any Excel object as a button or icon, or press Alt+F8 to select the macro, and then click Run.

    Please post back if you have any more problems or questions.

    +
    0 Votes
    ThumbsUp2

    You can't sort just one row. You would need to copy each of the even numbered rows to a new sheet, sort them there, then use the contents of that 2nd sheet to replace the even rows on the first sheet.