VBA Store Values In Array Based On Criteria Then Pass Out

[Lewzerrrr]

Hi,

In a part of my code I need to use a For Each Cell In Array("","","").. so I thought maybe I should dynamically do this using an array variable but i'm stuck..

What I'd like it to be is like something along the lines of below. The Sheet8.Cells is also defined as [UserRange] but I couldn't get this to work. Any help?

Dim MyArr() as Variant

For I = 1 to 28

 If Sheet8.Cells(I + 2, 4) = [MyName] then MyArr(I) = Sheet8.Cells(I + 2, 4)

Next I

For Each Cell In Array(MyArr)

CODE HERE

Next Cell

 

[wideboydixon]

You could use a collection instead:

Dim MyArr As New Collection


For I = 1 To 28
    If Sheet8.Cells(I + 2, 4) = [MyName] Then MyArr.Add Sheet8.Cells(I + 2, 4), Sheet8.Cells(I + 2, 4).Address
Next I


For Each Cell In MyArr
    ' CODE HERE
Next MyCell

WBD

 

[Fluff]

How about

Dim MyArr() As Range

For i = 1 To 28
   If Sheet8.Cells(i + 2, 4) = [myname] Then
      ReDim Preserve MyArr(1 To i)
      Set MyArr(i) = Sheet8.Cells(i + 2, 4)
   End If
Next i

For Each Cell In MyArr

'code Here

Next Cell