Redim a dynamic 2D array error - subscript out of range

[Toffes]

I am writing a VBA code which loops through a defined range in a worksheet, finds the filled cells within its limits and assigns the x,y values of each cell into an array.

This is my code:

Sub FindCells()

Dim lWidth as Integer, lHeight as Integer 'both of these parameters are subject to change by user's choice
                                                        'and can get the values of 10 to 500 and define the range (rows and columns)

Dim Molecules() As Variant
 ReDim Molecules(1 To lHeight, 1 To lWidth)


For Y = LBound(Molecules, 1) To UBound(Molecules, 1)    'rows (y)
  For x = LBound(Molecules, 2) To UBound(Molecules, 2)     'columns (x)
  
    If Cells(Y, x).Interior.ColorIndex <> xlNone Then
          
         Molecules = Array(Y, x)
           MsgBox "the address of original cells is = " & Molecules(1) & ", " & Molecules(2)

    End If

  Next x
Next Y

End Sub

After the first round, I get an error '9' - subscript out of range.
I have looked anywhere over the internet, and stumbled upon ReDim Preserve, and Transpose (which I'm not familiar with), and tried everything I could, but I just can't get to settle the problem.

I'd appreciate any help on how to rewrite my code.

 

[mikerickson]

You need to set the values for lHeight and lWidth before the ReDim statement.
Redim Molecules(1 To 0, 1 To 0) is an error, because 0 < 1.

 

[Toffes]

mikerickson, I have the values set by user's choice, as I said. I am testing my code over 20 x 20 range, meaning lWidth = 20 and l Height = 20.

 

[mikerickson]

In words, what is the purpose of this routine.
I see it looping through a range of cells, looking for those that have been colored.

Once you've found one of those cells, what do you want to do with it?

 

[Toffes]

In words, what is the purpose of this routine.
I see it looping through a range of cells, looking for those that have been colored.

Once you've found one of those cells, what do you want to do with it?

I have to insert the X,Y Values into an array, assign an offset to each cell, a new position for them and color the new cell while deleting the old one. I have all of that written in a working code, I just get that "subscript out of range" error for the loop itself.

 

[mikerickson]

My question was about what you wanted to do, it wasn't about the method.
"X Y value" is not an excel object, it sounds like it is related to the method you are using rather than the result you seek.

You want to find the colored cells in a range and do what?

You mentioned that you have working code for "positioning them and coloring the new cell".
What is that code and how is it called in relation to the sub in the OP.

 

[Toffes]

My question was about what you wanted to do, it wasn't about the method.
"X Y value" is not an excel object, it sounds like it is related to the method you are using rather than the result you seek.

You want to find the colored cells in a range and do what?

You mentioned that you have working code for "positioning them and coloring the new cell".
What is that code and how is it called in relation to the sub in the OP.

OK, so here is the full code I'm working with. My actual purpose is to assign an XY value to each cell and replace its position within the array, and to witness the results I color the new cell and erase the old one.

Sub FindCells()

Dim lWidth as Integer, lHeight as Integer 'both of these parameters are subject to change by user's choice
                                                        'and can get the values of 10 to 500 and define the range (rows and columns)

Dim Molecules() As Variant
 ReDim Molecules(1 To lHeight, 1 To lWidth)


For Y = LBound(Molecules, 1) To UBound(Molecules, 1)    'rows (y)
  For x = LBound(Molecules, 2) To UBound(Molecules, 2)     'columns (x)
  
    If Cells(Y, x).Interior.ColorIndex <> xlNone Then
          
         Molecules = Array(Y, x)
           MsgBox "the address of original cells is = " & Molecules(1) & ", " & Molecules(2)

          Randomize
           dX = Int((H - L + 1) * Rnd() + L) 'speed vector x
            dY = Int((H - L + 1) * Rnd() + L) 'speed vector y    
 
          Vector = Array(dY, dX)
         MsgBox "the speed vector is = " & Vector(1) & ", " & Vector(2)




             NewX = x + dX    'new position
             MsgBox "newx=" & NewX
              NewY = Y + dY     'new position
             MsgBox "newy=" & NewY


     Molecules(1) = NewY
      Molecules(2) = NewX

       NewPos = Array(Molecules(1), Molecules(2))
        Molecules = NewPos

     Worksheets("Main Screen").Cells(Y, x).Interior.ColorIndex = 0   'erase the old cell
       NewY = Molecules(1)
        NewX = Molecules(2)
          Cells(NewY, NewX).Interior.Color = 3  'fill with different color

    End If

  Next x
Next Y

End Sub

 

[mikerickson]

That is not working code.
You dimension Molecules as a 2 dimensional array, but every reference there after is either to the whole array
Molecules = Array(Y, x)

or using the syntax for a one dimensional array

Molecules(1) = NewY

That code doesn't run.

You keep talking about a new cell. Where is this new cell in relation to the old colored cell?

 

[Toffes]

Pardon me, this is the code-type reply.
OK, so here is the full code I'm working with. My actual purpose is to assign an XY value to each cell and replace its position within the array, and to witness the results I color the new cell and erase the old one.

[COLOR=#333333][COLOR=#333333]Sub FindCells()

Dim lWidth as Integer, lHeight as Integer 'both of these parameters are subject to change by user's choice
                                                       'and can get the values of 10 to 500 and define the range (rows and columns)
'Let's say
lWidth = 10
lHeight = 10

Dim Molecules() As Variant
    ReDim Molecules(1 To lHeight, 1 To lWidth)

For Y = LBound(Molecules, 1) To UBound(Molecules, 1) 'rows (y)
    For x = LBound(Molecules, 2) To UBound(Molecules, 2) 'columns (x)

      If Cells(Y, x).Interior.ColorIndex <> xlNone Then

        Molecules = Array(Y, x)
          MsgBox "the address of original cells is = " & Molecules(1) & ", " & Molecules(2)

[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana]Randomize[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana] dX = Int((H - L + 1) * Rnd() + L) 'speed vector x[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana]  dY = Int((H - L + 1) * Rnd() + L) 'speed vector y [/FONT][/COLOR][/COLOR]

[COLOR=#333333][COLOR=#333333][FONT=Verdana]Vector = Array(dY, dX)[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana] MsgBox "the speed vector is = " & Vector(1) & ", " & Vector(2)[/FONT][/COLOR][/COLOR][COLOR=#333333][COLOR=#333333][FONT=Verdana]
[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana]NewX = x + dX 'new position[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana]  MsgBox "newx=" & NewX[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana]NewY = Y + dY 'new position[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][FONT=Verdana]  MsgBox "newy=" & NewY
[/FONT][/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]Molecules(1) = NewY
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]  Molecules(2) = NewX

[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]NewPos = Array(Molecules(1), Molecules(2))[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]   Molecules = NewPos[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333]
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]Worksheets("Main Screen").Cells(Y, x).Interior.ColorIndex = 0 'erase the old cell[/COLOR][/COLOR]
[COLOR=#333333][COLOR=#333333][COLOR=#222222][FONT=Verdana]   NewY = Molecules(1)[/FONT][/COLOR][/COLOR][/COLOR]
[COLOR=#333333]     NewX = Molecules(2)[/COLOR]
[COLOR=#333333]        Cells(NewY, NewX).Interior.Color = 3 'fill with different color[/COLOR]
[COLOR=#333333][COLOR=#333333]
End If

Next x
Next Y
[/COLOR][/COLOR][COLOR=#333333][COLOR=#333333]
End Sub[/COLOR][/COLOR]

 

[Toffes]

That is not working code.
You dimension Molecules as a 2 dimensional array, but every reference there after is either to the whole array
Molecules = Array(Y, x)

or using the syntax for a one dimensional array

Molecules(1) = NewY

That code doesn't run.

You keep talking about a new cell. Where is this new cell in relation to the old colored cell?

What I was trying to do is, if you have the

Molecules

array, and if I assign X,Y values to it, then after I get that NewY NewX values, I assign the new values to the same exact Molecules array of that same cell so it will move across the array. I might be wrong though...