Jumping back to a cell

[Guest]

Hello

within vb I read some values from my worksheet into a 2-dim table. The values of the cells and their references are kept in this table. After a sort, I want to change the property of the cells that keep the 5 highest values. So, next I want to jump back to these cells by their cell reference that is kept in the second column of my table. Unfortunately, after numerous attempts with e.g. ActiveCell etc... I did not get it to work

Does somebody have an idea

Highly appreciated
ZAkar.

 

[Patrick Molloy]

You could use the LARGE() worksheet function and save
yourself some time

If your array is sorted then you could re-size it

Sub GetTop5()

Dim MyArray As Variant 'MUST be variant

'load the data
MyArray = Range("MyList")
MyArray = SortTheArray(MyArray)
' drop it back somewhere
Range("G1:G5") = MyArray
End Sub

SortTheArray is my own function that gets back the top 5
cells


If your array has 5 rows & two columns, where column 1 is
the value and column 2 the cells address

dim Target as Range
Target = Range("G1")
for rw = 0 to 4
Target.Offset(i,0) = mArray(rw,0)
Target.Offset(i,1) = mArray(rw,1)
next

-----Original Message-----
Hello,

within vb I read some values from my worksheet into a 2-

dim table. The values of the cells and their references
are kept in this table. After a sort, I want to change
the property of the cells that keep the 5 highest values.
So, next I want to jump back to these cells by their cell
reference that is kept in the second column of my table.
Unfortunately, after numerous attempts with e.g.
ActiveCell etc... I did not get it to work.

 

[Guest]

if you use the .address Property to store the cell reference, you can use the Range.Method to retrieve it
MyArray(x, y) = MyCell.Addres
Range(MyArray(x, y)).Select

 

[Guest]

Thank you very much, Chris. It works now! Also Thanks to Patrick Molloy, appreciate your help a lot

Grtz
ZAkar

----- chris wrote: ----

if you use the .address Property to store the cell reference, you can use the Range.Method to retrieve it
MyArray(x, y) = MyCell.Addres
Range(MyArray(x, y)).Select