Filling array with several values and extent array using these values

Hi all,

I have values which are used to fill an array. Each value is related to a column and row.

The values are:

12 (R1C1)
28 (R2C2)
35 (R3C3)
... (R4C4)
... (R5C5)
... (R..C..)

So the numbers are diagonally put into the array and adjacent values (left and right depending on in which column the above value is located) need to be calculated using a user defined interval (10), resulting in the below array:

12, 22, 32, ...
18, 28, 38, ...
15, 25, 35, ...
..., ..., ..., ...

Can someone help me with this and its VBA code? The number of values will most likely be extended to 20. Hence, an array of 20 x 20 will be required.

Regards,
Martijn

I tried this, and it allows me to populate the array diagonally but then I get stuck with calculating the remaining values.
Please take notice, that due to the my inexperience with VBA code and arrays like this, I started with only 3 values.

Please Login or Register  to view this content.

For populating the remainder of the array I tried where Ival is the interval (10) and "l" (a counter to multiply the interval value every time the loop is restarted). But this does not work properly.

Please Login or Register  to view this content.

Any suggestions? I must be way off most likely .

Norie,

Thanks! That works like I needed. However, how do you get values from cells to an array like you used for arrStart?

I have 20 values which are in one row, but different adjacent cells. Is there some way of populating arrStart using these values?

Thanks, Martijn

Hi Norie,

Here it is .


Example.xlsm

Greetings,

I was suggesting this:

Please Login or Register  to view this content.

But Norie's code should be faster rather then using a loop (right Norie?)

Regards,
Daniel

Originally Posted by dafer660

Greetings,

I was suggesting this:

Please Login or Register  to view this content.

But Norie's code should be faster rather then using a loop (right Norie?)

Regards,
Daniel

This loop was meant to "map" the array but your way is cleaner.

Norie, Daniel,

Thanks for your help! It works perfectly. I have another question, which is an extension of the code.

Once the numbers, as the previous code does, are generated, the numbers need to be included in a listing which is based on an user defined interpolation interval
between a minimum and maximum value. In the attached file I took an example interval of 1.5 m and 3 listings for column 1 to 3. However, a total of 20 columns can be populated depending on the user input. The red marked cells show the location where the value from the results of each column are to be included. This means that part of the second table need to be shifted down each time a value is included. Can you help me with this?

Many thanks in advance,
Martijn


Example 2.xlsm

Hi Norie, Daniel,

I have found a solution, but I am not sure if this is the cleanest solution. Can you give me advise?

Please Login or Register  to view this content.

Thanks in advance,
Martijn

For some reason my code does not function if I adjust the sheet input values. See example 3.


Example 3.xlsm

Help would be much appreciated!