Macro to Reverse Order 1

[rnordquest]

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 = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
y = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
z = ActiveCell.Value
Next n
Cells(sRow, 1).Select
For n = npts To 1 Step -1
x = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
y = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
z = ActiveCell.Value
Next n
ActiveCell.Offset(4, 1).Activate
npts = ActiveCell.Value

Loop


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

Thanks,

Roger

 

[brengine]

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

'  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

 

[electricpete]

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.

 

[rnordquest]

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.

 

[cummings54]

Here's another approach you may find interesting.

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

 

[rnordquest]

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

 

[rnordquest]

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 = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
y = ActiveCell.Value
ActiveCell.Offset(0, 1).Activate
z = ActiveCell.Value
ActiveCell.Offset(1, -2).Activate
Next n
Cells(sRow, 1).Select
For n = npts To 1 Step -1
ActiveCell.Value = x
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = y
ActiveCell.Offset(0, 1).Activate
ActiveCell.Value = z
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.

 

[brengine]

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

 

[rnordquest]

Why doesn't this work?

Dim x()

Isn't that supposed to do it dynamically?

 

[cummings54]

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

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
[COLOR=black yellow]
-0.042282194 1.500317076 -0.064645944
-0.022277818 1.500350089 -0.086256151
-0.002509365 1.500382076 -0.107855452
.
.
.
[/color]
run the code and you get
[COLOR=black green]
.
.
.
-0.002509365 1.500382076 -0.107855452
-0.022277818 1.500350089 -0.086256151
-0.042282194 1.500317076 -0.064645944
[/color]
HTH and is what you are looking for

 

[electricpete]

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

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.

 

[electricpete]

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


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

 

[electricpete]

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

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

 

[electricpete]

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.

 

[cummings54]

electricpete,
If formulas you want, formulas you get :-D
Just change the array assignment as follows:

a = Selection.Formula

 

[electricpete]

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:

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.

 

[brengine]

Roger,

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

 

[electricpete]

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.

 

[rnordquest]

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.