Swapping Values

[Don Lloyd]

Hi,

I'm working on a customised sort routine which involves swapping values
within a table.
Uing a temporary variable, single cells are not a problem, but what's the
best way to work with multiple cells. The code is in R1C1 notation.

Example
Swap Row 10, Columns 5 to 8
With Row 15, Columns 5 to 8

What I would like to be able to do is to complete the swap using one
temporary variable.

Regards,
Don Lloyd

 

[Guest]

Declare range objects and set them

dim rngHigh as range
dim rngLow as range
dim rngTemp as range

set rngHigh = ...
set rngLow = ...
set rngTemp = ...

 

[keepITcool]

if the ranges are similar size then use a 'standard'
swap approach like:

dim v
v = rng1.value
rng1.value = rng2.value
rng2.value = v

What i dont understand that IF you write a sort routine,
you make 'intermediate writes' to cells..
which imho would make it unacceptably slow.


I'd EITHER let excel do all the work

OR
I'd read the entire range into an array.
Then SORT the array
Then WRITE the sorted array back to the range.

keepITcool


< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Don Lloyd]

Thanks

I did try that, but having set the range how does it go to another location
in the table?

Don

 

[Don Lloyd]

Thanks Ron,

I got that to work OK

The "Sort" is really a re-arrangement of data in line with pre-defined
criteria.
I can't imagine an Excel way of doing it.
The table is at most 20 rows by 8 columns.
Although many iterations are sometimes required, it doesn't take long.
In fact, I needed to view and check results in progress - to do this
effectively I had to include a delaying loop to slow things down.
Having said that I have only tested up to 12 rows by 8 columns - if it slows
down too much with 20 rows, I'll take your advice and use an array.

Thanks again,
Don