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
' 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
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
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
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
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
CODE
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
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
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
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
Dim x()
Isn't that supposed to do it dynamically?
RE: Macro to Reverse Order
CODE
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
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
CODE
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
"...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
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Macro to Reverse Order
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
If formulas you want, formulas you get
Just change the array assignment as follows:
CODE
RE: Macro to Reverse Order
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
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
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
=====================================
Eng-tips forums: The best place on the web for engineering discussions.
RE: Macro to Reverse Order
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.