Re: "Flipping" a matrix

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Thanks Jim for coming back.

I was half anticipating you were going to say something about large
"formula" arrays, particularly returning them. But I assume the routines you
refer to, in contrast to "FlipTheSelection", merely return data (values); so
I can't quite envisage why there would be a problem with dumping an array in
one go, even with 40k elements.

However I will certainly keep your comments in mind and test both ways with
largest anticipated volumes, possibly break down into a subset of arrays.

I have briefly tried your Permutations and List Files programs. Both very
nice and thanks for making them available.

Regards,
Peter T

"Jim Cone" <jim.coneXXX@xxxxxxxxxx> wrote in message
news:OgOUZonKGHA.140@xxxxxxxxxxxxxxxxxxxxxxx
Hello Peter,

I know of no empirical method to determine which is fastest.
It is "cut and try". In my List Files program, some of the
data is written directly to the work*** because when I improved
it, by writing to arrays, it took longer to finish.

With my Permutations program...with an array of 30,000
to 40,000 items...it would almost grind to a halt when
trying to place the array on the ***. That could be memory
problems caused by the recursive function it uses?
And writing the list directly goes very fast. The progress bar
that is used is for the check spelling part of the program.

I have not seen any discussion on the subject.

Regards,
Jim Cone
San Francisco, USA
Both programs mentioned above are free and available here...
http://www.realezsites.com/bus/primitivesoftware





"Peter T" <peter_t@discussions> wrote in message
news:%23RDOMZnKGHA.2064@xxxxxxxxxxxxxxxxxxxxxxx
Hi Jim,

And yes, I have also discovered that one cannot assume
writing a Variant(containing an array) or Arrays directly
to a range is faster then plunking data directly in each cell.

I know you would not say that without a good reason so I am curious, I had
always assumed just that - ie very significantly faster to read as an
array,
do whatever in a helper redim'ed array and write back to cells in one go.

all versions incl xl97

Dim v As Variant
With Selection
v = Selection.FormulaR1C1
.Offset(, .Columns.Count) = v
End With

Regards,
Peter T

"Jim Cone" <jim.coneXXX@xxxxxxxxxx> wrote in message
news:eDPpMolKGHA.1424@xxxxxxxxxxxxxxxxxxxxxxx
kIc,

Thanks for your comments.

I like your approach, but as a general practice will not
write code that won't run in xl97 (assigning to arrays).

And yes, I have also discovered that one cannot assume
writing a Variant(containing an array) or Arrays directly
to a range is faster then plunking data directly in each cell.

If I was starting over with it, I believe that filling an index
row and sorting would be the way to go.

Regards,
Jim Cone
San Francisco, USA


"keepITcool" <xrrcvgpbby@xxxxxxxxx> wrote in message
news:xn0ei4bgt2thdkv00ckeepitcoolnl@xxxxxxxxxxxxxxxxxxxxx
Jim,

I love your select case for the verification process, a gem!
BUT:
Using FormulaR1C1 iso FormulaLocal will screwup fewer formulas
(personally i avoid all Local string like the plague, more important
to use r1c1)
FWIW:
Textually confusing:
vbYes to Reverse Column data
to the programmer means flip the data in each column,
but to a user "Yes to Flip ROWS" is clearer.
No to flip COLUMNS" is probably clearer.
When a user breaks you should finish flipping at least the current
column. (I'd use an intermediate array(1 to rows,1 to 1) to cache the
"writedata" and avoid writing to single ranges

Following code uses 2 arrays
and can be copied into your routine...

(drawback: the writing part may be unresponsive)

Dim DataArray(), lRow&, lCol&
Application.StatusBar = "Reading"
DataArray = Rng.FormulaR1C1
ReDim CellArray(1 To Rws, 1 To Cols)
Application.StatusBar = "Flipping"
For lRow = 1 To Rws
For lCol = 1 To Cols
If Response = vbNo Then
CellArray(lRow, Cols + 1 - lCol) = DataArray(lRow, lCol)
Else
CellArray(Rws + 1 - lRow, lCol) = DataArray(lRow, lCol)
End If
Next
Next
Application.StatusBar = "Writing Flipped data"
Rng.FormulaR1C1 = CellArray
--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Jim Cone wrote :
John,

You are about the first person in about 4 years (that I know about)
that has had any interest in flipping a range. Maybe you or somebody
can use this code. I like Tom's idea of just sorting the data, but
this works and it is turnkey...





.

Quantcast