Assign values from cells to array and use it in loops

[nagovind]

Dear All

Need the VBA code with arrays in the below manner.

Dim pnum(0 To 10000) As Variant

pnum(1) = 2
pnum(2) = 64
pnum(3) = 126
pnum(4) = 188
pnum(5) = 250
pnum(6) = 312
pnum(7) = 374
pnum(8) = 436
pnum(9) = 498
pnum(10) = 560

10 different numbers has to be stored in an array
This data is already available in a sheet name "Sheet1" in the range A1 to A10

Please advise how to assign these 10 numbers stored in a 10 different cells in "Sheet1" into an array named pnum

Further please advise how to use the data in the stored number in the array in the loop

For i = 0 to 1000
if pnum(i) = i then
k = k+1
Endif
Next i

Please advise
Regards
Govind

 

[NARAYANK991]

Hi Govind ,

1. Even when you assign a one-dimensional range , such as a one column or a one row range , Excel still dimensions the resulting array with 2 dimensions , not 1.

2. Assigning a range to a VBA variable is possible provided the variable has been declared as of type Variant.

In your case , the following will suffice :

Dim pnum As Variant

pnum = ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Value

3. Accessing the individual elements of the array can be done as follows :

pnum(i,1)

where i will range from 1 through 10.

If it were a horizontal range being assigned to an array , the individual elements can be accessed by :

pnum(1,i)

where i will range from 1 through 10.

Narayan

 

[nagovind]

Dear Narayan,

Thanks a lot for your assistance

Regards
Govind

 

[Hui]

Govind

The advantage of this is that you can edit the array in VBA and then write it back in a single line as:

ThisWorkbook.Worksheets("Sheet1").Range("A1:A10").Value = pnum

 

[nagovind]

Dear Hui,

Thanks for you reply...
Yes agreed.
Initially i did the code with manual entry/ assignment of value to the array as shown in first post as i was not aware of the code further the value keep on changes...
Now it made it easy
Your reply is additional tips for me"edit the array in VBA and then write it back in a single line"
Thanks a lot

Regards
Govind