×
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

Macro to Reverse Order

Macro to Reverse Order

Macro to Reverse Order

(OP)
I want to reverse the order of each section and tried this.  What did I do wrong?

'  Reorder each section

    Range("B4").Select
    npts = ActiveCell.Value
    Do Until npts < 1
        ActiveCell.Offset(1, -1).Activate
        sRow = ActiveCell.Row
        For n = 1 To npts
            x(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            y(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            z(n) = ActiveCell.Value
        Next n
        Cells(sRow, 1).Select
        For n = npts To 1 Step -1
            x(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            y(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            z(n) = ActiveCell.Value
        Next n
        ActiveCell.Offset(4, 1).Activate
        npts = ActiveCell.Value
        
    Loop


I get an error on the first x(n) line for end of statement.  Will the check for npts<1 be met when I run out of data at the bottom?

Thanks,

Roger

RE: Macro to Reverse Order

Any lines I added are all the way Left Justified. Anything of your's I wanted to change, I commented out and then copied/changed on the next line.

I suggest you use cells(sRow, sCol).select (or .value, etc...) in the future. It can be easier to manage than the Offset command (at least that's my preference).

Enjoy,
Ken

CODE

'  Reorder each section

Dim x() As String
Dim y() As String
Dim z() As String


    Range("B4").Select
    npts = ActiveCell.Value
    Do Until npts < 1
        ActiveCell.Offset(1, -1).Activate
        sRow = ActiveCell.Row
sCol = ActiveCell.Column
        
ReDim x(npts)
ReDim y(npts)
ReDim z(npts)
        For n = 1 To npts
Cells(((sRow - 1) + n), sCol).Select
            x(n) = ActiveCell.Value
            
            ActiveCell.Offset(0, 1).Activate
            y(n) = ActiveCell.Value
            
            ActiveCell.Offset(0, 1).Activate
            z(n) = ActiveCell.Value
        
        Next n
        Cells(sRow, 1).Select
n_2 = 1
        For n = npts To 1 Step -1
            
Cells(((sRow - 1) + n_2), sCol).Select
            'x(n) = ActiveCell.Value
ActiveCell.Value = x(n)
            
            ActiveCell.Offset(0, 1).Activate
            'y(n) = ActiveCell.Value
ActiveCell.Value = y(n)
            
            ActiveCell.Offset(0, 1).Activate
            'z(n) = ActiveCell.Value
ActiveCell.Value = z(n)
n_2 = n_2 + 1
        Next n
        ActiveCell.Offset(4, 1).Activate
        npts = ActiveCell.Value
        
    Loop
End Sub

RE: Macro to Reverse Order

I haven't studied the code and not sure exactly what you're trying to accomplish.

IF you want to reverse the order of some rows of data - that's a snap.  Add an extra column for an index number in ascending order.  Then sort on that column in descending order.

Sorry if I misunderstood the question.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

(OP)
I just had "Dim x()" y & z at the top.  Will my numbers be affected by Dim As String?  And I did have the equations turned around backwards.  I'd have caught that one the first time I got that far.  Thanks for the help.

And you understood the ?.  I guess I could write the macro to add an extra index column to sort on.  Thanks pete.

RE: Macro to Reverse Order

Here's another approach you may find interesting.

CODE

Sub test()
  Dim b As Range
  a = Selection
  Set b = Selection.Cells(1, 1)
  n = UBound(a)
  For irow = 0 To n - 1
    b.Offset(irow, 0).Formula = a(n - irow, 1)
  Next
End Sub

RE: Macro to Reverse Order

(OP)
cummings, I'll need help understanding this one and how to apply it to the data.  Here's an example.


Header
Header
Header            
NPTS 13    13        
-0.042282194    1.500317076    -0.064645944    
-0.022277818    1.500350089    -0.086256151    
-0.002509365    1.500382076    -0.107855452    
.
.
.
Header
Header
Header            
NPTS 25    25


and so on

RE: Macro to Reverse Order

(OP)
Ken,  I had to move the ReDim inside the Do loop because npts is something new for each set of data.

Here's what worked:

    Dim x()
    Dim y()
    Dim z()

'  Reorder each section

    Range("B4").Select
    npts = ActiveCell.Value
    Do Until npts < 1
        ReDim x(npts)
        ReDim y(npts)
        ReDim z(npts)
        ActiveCell.Offset(1, -1).Activate
        sRow = ActiveCell.Row
        For n = 1 To npts
            x(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            y(n) = ActiveCell.Value
            ActiveCell.Offset(0, 1).Activate
            z(n) = ActiveCell.Value
            ActiveCell.Offset(1, -2).Activate
        Next n
        Cells(sRow, 1).Select
        For n = npts To 1 Step -1
            ActiveCell.Value = x(n)
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = y(n)
            ActiveCell.Offset(0, 1).Activate
            ActiveCell.Value = z(n)
            ActiveCell.Offset(1, -2).Activate
        Next n
        ActiveCell.Offset(3, 1).Activate
        npts = ActiveCell.Value
        
    Loop

I don't need the sRow line.  Thanks a bunch.  I'd never have figured out the ReDim.

RE: Macro to Reverse Order

Roger,

I've also seen people just initialize the array at some overly large value, like:
Dim x(1000)
Dim y(1000)
Dim z(1000)

They figure there's no way they'll ever need 1000 rows of data, and they don't want to mess with ReDim (or maybe don;t know how or that they will even need to resize it).

Also/Or, if you want to resize an array but not loose you're existing values, look up ReDim Preserve.

And since we're on the subject, to find the size of an array you would do somthing like:
Debug.Print Ubound(x())

Probably more information than you were looking for, but these are some tricks I've learned when using arrays.

Later,
Ken
 

RE: Macro to Reverse Order

(OP)
Why doesn't this work?

Dim x()

Isn't that supposed to do it dynamically?

RE: Macro to Reverse Order

Roger, I've made some minor changes to the rest code as follws:

CODE

Sub test()
  Dim b As Range
  a = Selection
  Set b = Selection.Cells(1, 1)
  n = UBound(a)
  For irow = 0 To n - 1
    b.Offset(irow, 0).Formula = a(n - irow, 1)
    b.Offset(irow, 1).Formula = a(n - irow, 2)
    b.Offset(irow, 2).Formula = a(n - irow, 3)
  Next
End Sub
The way it works...
First select the data range you want reversed.  In your sample data it would be

-0.042282194    1.500317076    -0.064645944    
-0.022277818    1.500350089    -0.086256151    
-0.002509365    1.500382076    -0.107855452    
.
.
.

run the code and you get

.        
.        
.        
-0.002509365    1.500382076    -0.107855452
-0.022277818    1.500350089    -0.086256151
-0.042282194    1.500317076    -0.064645944

HTH and is what you are looking for

RE: Macro to Reverse Order

Yup, that'll work.  A small modification will allow it to work regardless of number of rows in the selected range:

CODE

Sub test()
  Dim b As Range
  a = Selection
  Set b = Selection.Cells(1, 1)
  n = UBound(a)
  For irow = 0 To n - 1
    For icol = 0 To Selection.Columns.Count - 1
     b.Offset(irow, icol).Formula = a(n - irow, 1)
    Next icol
  Next
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

Correction:
"...regardless of number of columns in the selected range:"
 

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

Whoops. Looks like that didn't work. Never mind.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

My modification to cummings code created some new problems.
There was a "problem" (depending how  you look at it.  cummings code will convert formula's to values.  The variant a array apparently stores the values rather than the formulas.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

electricpete,
If formulas you want, formulas you get big smile
Just change the array assignment as follows:

CODE

a = Selection.Formula

RE: Macro to Reverse Order

That does not give the intended result.  Relative formula's are not updated properly when you plug them in.

For example, put the following formulas starting in A1
1    =+A1*2    =+A1*3
2    =+A2*2    =+A2*3
3    =+A3*2    =+A3*3

Which of course displays as follows:
1    2    3
2    4    6
3    6    9



Run the macro with the change you mentioned and the resulting formulas are:
3    =+A3*2    =+A3*3
2    =+A2*2    =+A2*3
1    =+A1*2    =+A1*3

Which displays as:
3    2    3
2    4    6
1    6    9


I'm sure there is a way to fix it, but it won't work as it is.

Here is the sorting approach:

CODE

Sub reverser()
 Dim target As Range   ' holds the original range
 Dim myRow As Range ' Holds the current row of target during for loop
 
 Set target = Selection  ' Initialize target
 
 ' select first col of range in prep for adding sort column:
 target.Columns(1).EntireColumn.Select
 
' add column to the left of original selection:
 Selection.Insert Shift:=xlToRight
 
 ' Populate column to left of original range with a sorting index (row)
 For Each myRow In target.Rows
   myRow.Offset(0, -1).Resize(1, 1) = myRow.Row
 Next myRow
  
 
 ' Select the expanded range (including index column) in preparation for sorting:
 target.Offset(0, -1).Resize(target.Rows.Count, target.Columns.Count + 1).Select
 
 ' Sort:
 Selection.Sort Key1:=Selection.Cells(1, 1), Order1:=xlDescending, _
          Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
          Orientation:=xlTopToBottom
          
          
 Selection.Cells(1, 1).EntireColumn.Delete ' Gets rid of the index column
 
 ' reset the selection to its original size (one column less than expanded selection):
 Selection.Offset(0, 0).Resize(Selection.Rows.Count, Selection.Columns.Count - 1).Select
End Sub

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

Roger,

Quote:

Why doesn't this work?
Dim x()
Isn't that supposed to do it dynamically?

Arrays can be re-sized during macro execution (i.e. dynamic) by using the ReDim. So yes it is dynamic, but it does not automatically resize itself.

Ken

 

RE: Macro to Reverse Order

I forgot to mention one thing, for my macro you need to highlight a range of interest (any size, any location) and then run the macro to reverse the rows within that selection.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Macro to Reverse Order

(OP)
Now that I know how to reverse the section order, how do I ensure that the points go around the body in order?  This will end up in some grid generator and I don't want it to wrap back around itself.  I can't sort on x,y,or z because one or the other goes from negative to positive and back. These are 3D points around the leading edge of a very thin airfoil.  There must be some math that can determine this.  The offending points can just be deleted; I have way more than I need anyway.

The y plane has the least variation as the cuts were made that way.  Nevertheless, the points are of sufficient density that I basically have a cloud of points along a small band (y plane) around the airfoil.

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