I declare a dynamic array but don't know how to assign the cells.valu
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
Michae

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

If I wanted to populate an array of integers from the range A1 to AP1, some
or all of which may have entries, I'd do it like this:

Sub a()
Dim MyArray() As Integer
Dim Cell As Range, Counter As Integer
For Each Cell In Range("A1:AP1").SpecialCells(xlCellTypeConstants)
Counter = Counter + 1
ReDim Preserve MyArray(1 To Counter)
MyArray(Counter) = Cell.Value
Next
End Sub

--
Jim Rech
Excel MVP
"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/
|

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/

Hi
Your request is a bit vague, but I'll assume you want to populate
Wheel with the none empty values in your range. You will first of all
check that your range is not empty.
One way of many might be

Dim MyRange as Range
Set MyRange = Worksheets("MyWorkSheet").Range("A1:AP1")
ict = Application.Worksheetfunction.CountA(MyRange)
Dim Wheel()
If ict=0 then
MsgBox "There are no values!"
Else
ReDim Wheel(0 to ict-1)
Dim j as integer, i as integer
i = 0
With MyRange
For j = 1 to .Columns.Count
If .Cells(1, j).Value<"" then
Wheels(i) = .Cells(1, j).Value
i = i+1
end if
next j
end with
end if

regards
Paul

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/

It would speed things up to take the ReDim [Preserve] out of the loop:

Sub a()
Dim MyArray() As Integer, rng As Range
Dim iCell As Range, Counter As Integer
Set rng = Range("A1:AP1").SpecialCells(xlCellTypeConstants)
ReDim MyArray(1 To rng.Count)
Counter = 1
For Each iCell In rng
MyArray(Counter) = iCell.Value
Counter = Counter + 1
Next
End Sub

Alan Beban

Jim Rech wrote:
If I wanted to populate an array of integers from the range A1 to AP1, some
or all of which may have entries, I'd do it like this:

Sub a()
Dim MyArray() As Integer
Dim Cell As Range, Counter As Integer
For Each Cell In Range("A1:AP1").SpecialCells(xlCellTypeConstants)
Counter = Counter + 1
ReDim Preserve MyArray(1 To Counter)
MyArray(Counter) = Cell.Value
Next
End Sub

And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to your workbook and you can
stand a 20-millisecond or so hit in execution speed, the coding can be
somewhat simpler:

Sub(a)
Dim MyArray() As Integer
Assign Range("A1:AP1").SpecialCells(xlCellTypeConstants), MyArray
End Sub

Alan Beban

Alan Beban wrote:

It would speed things up to take the ReDim [Preserve] out of the loop:

Sub a()
Dim MyArray() As Integer, rng As Range
Dim iCell As Range, Counter As Integer
Set rng = Range("A1:AP1").SpecialCells(xlCellTypeConstants)
ReDim MyArray(1 To rng.Count)
Counter = 1
For Each iCell In rng
MyArray(Counter) = iCell.Value
Counter = Counter + 1
Next
End Sub

Alan Beban

Jim Rech wrote:

If I wanted to populate an array of integers from the range A1 to AP1,
some or all of which may have entries, I'd do it like this:

Sub a()
Dim MyArray() As Integer
Dim Cell As Range, Counter As Integer
For Each Cell In Range("A1:AP1").SpecialCells(xlCellTypeConstants)
Counter = Counter + 1
ReDim Preserve MyArray(1 To Counter)
MyArray(Counter) = Cell.Value
Next
End Sub

Thank you for helping me.

After the execution of the code, I get below

thenos = Str(Num(i)) + Str(Num(j)) + Str(Num(k)) + Str(Num(l))
Str(Num(m)) + Str(Num(n))

How do I insert the numbers in the cells row after row? i.e
num(i)-num(n) individually

I would like to add another count of (ISEVEN) of thenos,how to go abou
it?

The results will be as below example

1 , 2 , 3 , 14 , 22 , 26 4-this 4 is the count of EVEN

Regards
Michae

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