I am not exactly sure how you are going to use that array, but here is
something similar that I use. On sheet1 I have several named ranges where
the user inputs values in the cells, for example A1:A10 is a Named Range
"Range1", B1:B10 is named "Range2", etc. The goal is to find all the exact
matches for each value in a range, that is in Column "B" on Sheet2 and
delete the entire row. Note: it does not matter whether the user inputs
values in all 10 cells of the Named Ranges or not. Here is the code:

Option Explicit
Dim myArray As Variant

'With Sheet2 Activated
Sub Arry1 ()
myArray = ThisWorkbook.Names("Range1").RefersToRange.Value
Dim LastRow As Long
Dim FirstRow As Long
Dim iRow As Long

FirstRow = 1
LastRow = Cells(Rows.Count, "B").End(xlUp).Row 'Finds the last row in
column "B"

For iRow = LastRow To FirstRow Step -1
If IsError(Application.Match(Cells(iRow, "B").Value, myArray, 0))
Then
'do nothing
Else
Rows(iRow).Delete
End If
Next iRow
End Sub

Mike F

"Michael168 " wrote in message
...
I declare a dynamic array but don't know how to assign the cells.value
to the wheel
I use columns A1:AP1 to let the user key in each individual number.
From the range that have numbers, I use the counter as array size.
e.g

Sub Lwheel()

ict = WorksheetFunction.CountA(Range("A1:AP1"))
Dim wheel()
Redim Wheel(0 To ict)

Example the user key in A1=1,A2=2,A3=3,A4=4,A5=5,A6=6,A7=7,A8=8

So the array becomes as below;

Wheel(0)=A1 # 1
Wheel(1)=A2 # 2
Wheel(2)=A3 # 3
Wheel(3)=A4 # 4
Wheel(4)=A5 # 5
Wheel(5)=A6 # 6
Wheel(6)=A7 # 7
Wheel(7)=A8 # 8

My questions is how to assign the individual cells.value to wheel (0)
until wheel(7)?


End Sub

Regards
Michael


---
Message posted from http://www.ExcelForum.com/