Cell selection by cell matrix

Question

@TimWilliams if I define CellArray matrix as range then it crashes on the code to build the matrix, I followed the answer posted at the link that you have indicated https://stackoverflow.com/a/8320884/11835835

Dim CellsArray(3,3) As Range
For X = 0 To 2  
    For Y = 0 To 2  
        CellsArray(X, Y) = Cells(X+1,Y+1) _
        .Address(RowAbsolute:=False, ColumnAbsolute:=False)  'it crashes here run-time error 91 
    Next Y  
Next X

For K = 1 To 2  
    ActiveSheet.Union(Range(CellsArray(0, 0), CellsArray(0, K))).Select  
Next K

Instead it works if I define CellsArray matrix as string

Dim CellsArray(3,3) As String

but then it crashes on

ActiveSheet.Union(Range(CellsArray(0, 0), CellsArray(0, K))).Select

with run time error 438

What is Row, Column? There's no way to no what you are trying to do, but change that to `Application.Union(CellsArray...` — AAA, Jul 26 '19 at 15:29
row column are just index calculated by x, y that I've omitted — FastFurious, Jul 26 '19 at 15:44
`Cells(X+1,Y-3)` - these two arguments need to be at least 1 Does your code even compile? — Tim Williams, Jul 26 '19 at 16:36
@TimWilliams the code to build the matrix works well, but next step not, when I try to select cells with the matrix elements. If I write: `ActiveSheet.Range(CellsArray(0, 0), CellsArray(0, 1)).Select` it was selected a range, but I want to select non-adjacent cells. — FastFurious, Jul 27 '19 at 14:50
`ActiveSheet.Range(CellsArray(0, 0) & "," & CellsArray(0, 1) & "," & CellsArray(0, 2)).Select` — Tim Williams, Jul 28 '19 at 01:50
`For Y To 3` I think `Cells(X+1,Y-3)` don't work, but it was approximately, `Cells(X+1,Y+3)` I think it works well. However the code for selection works well `ActiveSheet.Range(CellsArray(0, 0) & "," & CellsArray(0, 1) & "," & CellsArray(0, 2)).Select` thanks to @TimWilliams — FastFurious, Jul 29 '19 at 08:33
there is a way to use this method in a for cycle? eg. `For K = 0 to 2 ActiveSheet.Range(CellsArray(0, K) & "," ).Select <-- I want to cycling my matrix inside Next K` — FastFurious, Jul 29 '19 at 13:56
You can use `Application.Union()` to build a range inside a loop. — Tim Williams, Jul 29 '19 at 14:40
don't work: `For K = 1 To 3 ActiveSheet.Union(CellsArray(0, K)).Select Next K` — FastFurious, Jul 29 '19 at 14:59
even don't work: `ActiveSheet.Union(Range(CellsArray(0, K))).Select` — FastFurious, Jul 29 '19 at 15:17
Did you try doing some research on how to use Union? There are plenty of examples here on SO alone. Eg: https://stackoverflow.com/questions/8320822/loop-through-cells-and-add-to-a-range — Tim Williams, Jul 29 '19 at 16:16
@TimWilliams if I define CellArray matrix as range then crash on the code to build the matrix — FastFurious, Jul 31 '19 at 15:00
It would help to update your question to add the actual code you're having problems with, and explain exactly what happens when it runs. — Tim Williams, Jul 31 '19 at 15:09
@TimWilliams Now my question is updated I've updated it, have some idea? — FastFurious, Aug 01 '19 at 08:31
Please fix your X and Y loops so we can tell what you're really doing. Eg: `For Y To 3` makes no sense and will not compile — Tim Williams, Aug 01 '19 at 14:53
now it is a simple script that gets in a square matrix 3x3 some cells in every elements. it starts from the top of the left cell(1,1) then follow to the righ to the cell(1,3), then passes to the second row, and place the cell from the (2,1), to the cell(2,3), and finally passes to the third row getting the cell(3,1) to cell(3,3). But then when I have the method I'll modify the gotten cell. But the next step does not work, because one time I have the matrix I want to select some cell of the row of the matrix as a ricursive method with a For cycle — FastFurious, Aug 02 '19 at 10:02

Tim Williams · Answer 1 · 2019-08-19T14:55:07.660

0

Try this:

Dim CellsArray(1 To 3, 1 To 3) As Range 'easier to use a 1-based array

For X = 1 To 3  
    For Y = 1 To 3  
        Set CellsArray(X, Y) = Cells(X, Y) 'Need Set here
    Next Y  
Next X

I'm not sure what you want to do here...

Dim rng As Range 
For K = 1 To 3 
    If rng is nothing then
        Set rng = CellsArray(1, 1)
    Else
        Set rng = Application.Union(rng, CellsArray(1, K)) 
    End If
Next K
rng.Select

edited Aug 19 '19 at 14:55

answered Aug 03 '19 at 04:16

Tim Williams

154,628
8
97
125

In this way the selection works but not as I want, because it was selected only the cell in CellsArray(1,1) and CellsArray(1,K=3) where K is the last index of the For cycle. It is losted the central element CellArray(1,K=2). Some Idea? – FastFurious Aug 05 '19 at 09:25
Someone have any idea? – FastFurious Aug 19 '19 at 09:46
Ok in this way it works very well. Thanks for the support. – FastFurious Aug 20 '19 at 17:17
I want to ask also how I can define the `rgn` as a empty array? I'd want to apply the above`K For` cycle inside an upper `Y For` cycle, and I need that `rgn` is empty when enter in the `K For` cycle. How I can define it ? – FastFurious Aug 20 '19 at 17:45
`Set rng = Nothing` – Tim Williams Aug 20 '19 at 17:50

Cell selection by cell matrix

1 Answers1