Martin1979
New Member
- Joined
- Mar 29, 2013
- Messages
- 9
Hello all,
I have a problem with the following:
I have multiple columns of numbers, which are sorted in ascending order and can be of variable length and interval, but always start at 0.
For this problem, I have selected 4 columns though.
<tbody>
</tbody>
Each column has a certain number, which is important to other calculations. This number can be everywhere in each column, but for ease of reference I selected 4 random numbers (bold).
Starting with the first column, the maximum value is determined via VBA (in this case 459.38). Using a user defined interval number (in this case 2), a new column is created which starts at 0 and adds 0.3 for each row until the maximum value of 7.25 has been reached.
However, the bold number is of importance too. So this value is to be included in the newly created column. So 3.25 in column 1 is to be included between 3.00 and 3.30 (see below)
<tbody>
</tbody>
After the first column, the other colums will get new intervals in a similar fashion as presented above. However, there will also be a fixed value added to each newly created row (interval), based on the value in the previous column. As example, a value of 0.2 has been used. In this case, the bold values will need to be included also.
<tbody>
</tbody>
However, the above table is incorrect and this is where my problem comes into play. Every time the bold value of the applicable column is inserted, the table needs to get a new line and the preceding and following columns need to get a new value, based on the bold value of the applicable column and user defined interval. The table below shows the result, the red marked values are the original values of the applicable column, which need to be included and the blue values are a result of this red value.
<tbody>
</tbody>
Can anyone help me with this problem? I am looking for a VBA solution for this problem.
Many thanks in advance,
Martin
I have a problem with the following:
I have multiple columns of numbers, which are sorted in ascending order and can be of variable length and interval, but always start at 0.
For this problem, I have selected 4 columns though.
0.00 | 0.00 | 0.00 | 0.00 |
0.25 | 0.3025 | 0.1600 | 0.2025 |
0.50 | 0.6050 | 0.3200 | 0.4050 |
0.75 | 0.9075 | 0.4800 | 0.6075 |
1.00 | 1.2100 | 0.6400 | 0.8100 |
1.25 | 1.5125 | 0.8000 | 1.0125 |
1.50 | 1.8150 | 0.9600 | 1.2150 |
1.75 | 2.1175 | 1.1200 | 1.4175 |
2.00 | 2.4200 | 1.2800 | 1.6200 |
2.25 | 2.7225 | 1.4400 | 1.8225 |
2.50 | 3.0250 | 1.6000 | 2.0250 |
2.75 | 3.3275 | 1.7600 | 2.2275 |
3.00 | 3.6300 | 1.9200 | 2.4300 |
3.25 | 3.9325 | 2.0800 | 2.6325 |
3.50 | 4.2350 | 2.2400 | 2.8350 |
3.75 | 4.5375 | 2.4000 | 3.0375 |
4.00 | 4.8400 | 2.5600 | 3.2400 |
4.25 | 5.1425 | 2.7200 | 3.4425 |
4.50 | 5.4450 | 2.8800 | 3.6450 |
4.75 | 5.7475 | 3.0400 | 3.8475 |
5.00 | 6.0500 | 3.2000 | 4.0500 |
5.25 | 6.3525 | 3.3600 | 4.2525 |
5.50 | 6.6550 | 3.5200 | 4.4550 |
5.75 | 6.9575 | 3.6800 | 4.6575 |
6.00 | 7.2600 | 3.8400 | 4.8600 |
6.25 | 7.5625 | 4.0000 | 5.0625 |
6.50 | 7.8650 | 4.1600 | 5.2650 |
6.75 | 8.1675 | 4.3200 | 5.4675 |
7.00 | 4.4800 | 5.6700 | |
7.25 | 4.6400 | 5.8725 | |
4.8000 | |||
4.9600 | |||
5.1200 | |||
5.2800 | |||
5.4400 |
<tbody>
</tbody>
Each column has a certain number, which is important to other calculations. This number can be everywhere in each column, but for ease of reference I selected 4 random numbers (bold).
Starting with the first column, the maximum value is determined via VBA (in this case 459.38). Using a user defined interval number (in this case 2), a new column is created which starts at 0 and adds 0.3 for each row until the maximum value of 7.25 has been reached.
However, the bold number is of importance too. So this value is to be included in the newly created column. So 3.25 in column 1 is to be included between 3.00 and 3.30 (see below)
0.00 |
0.30 |
0.60 |
0.90 |
1.20 |
1.50 |
1.80 |
2.10 |
2.40 |
2.70 |
3.00 |
3.25 |
3.30 |
3.60 |
3.90 |
4.20 |
4.50 |
4.80 |
5.10 |
5.40 |
5.70 |
6.00 |
6.30 |
6.60 |
6.90 |
7.20 |
<tbody>
</tbody>
After the first column, the other colums will get new intervals in a similar fashion as presented above. However, there will also be a fixed value added to each newly created row (interval), based on the value in the previous column. As example, a value of 0.2 has been used. In this case, the bold values will need to be included also.
0.00 | 0.20 | 0.40 | 0.60 |
0.30 | 0.50 | 0.70 | 0.90 |
0.60 | 0.80 | 1.00 | 1.20 |
0.90 | 1.10 | 1.30 | 1.50 |
1.20 | 1.40 | 1.60 | 1.80 |
1.50 | 1.70 | 1.90 | 2.025 |
1.80 | 2.00 | 1.92 | 2.10 |
2.10 | 2.30 | 2.20 | 2.40 |
2.40 | 2.42 | 2.50 | 2.70 |
2.70 | 2.60 | 2.80 | 3.00 |
3.00 | 2.90 | 3.10 | 3.30 |
3.25 | 3.20 | 3.40 | 3.60 |
3.30 | 3.50 | 3.70 | 3.90 |
3.60 | 3.80 | 4.00 | 4.20 |
3.90 | 4.10 | 4.30 | 4.50 |
4.20 | 4.40 | 4.60 | 4.80 |
4.50 | 4.70 | 4.90 | 5.10 |
4.80 | 5.00 | 5.20 | 5.40 |
5.10 | 5.30 | 5.70 | |
5.40 | 5.60 | ||
5.70 | 5.90 | ||
6.00 | 6.20 | ||
6.30 | 6.50 | ||
6.60 | 6.80 | ||
6.90 | 7.10 | ||
7.20 | 7.40 | ||
7.70 | |||
8.00 |
<tbody>
</tbody>
However, the above table is incorrect and this is where my problem comes into play. Every time the bold value of the applicable column is inserted, the table needs to get a new line and the preceding and following columns need to get a new value, based on the bold value of the applicable column and user defined interval. The table below shows the result, the red marked values are the original values of the applicable column, which need to be included and the blue values are a result of this red value.
0.00 | 0.20 | 0.40 | 0.60 |
0.30 | 0.50 | 0.70 | 0.90 |
0.60 | 0.80 | 1.00 | 1.20 |
0.90 | 1.10 | 1.30 | 1.50 |
1.20 | 1.40 | 1.60 | 1.80 |
1.425 | 1.625 | 1.825 | 2.025 |
1.50 | 1.70 | 1.90 | 2.10 |
1.52 | 1.72 | 1.92 | 2.12 |
1.80 | 2.00 | 2.20 | 2.40 |
2.10 | 2.30 | 2.50 | 2.70 |
2.12 | 2.42 | 2.62 | 2.82 |
2.40 | 2.60 | 2.80 | 3.00 |
2.70 | 2.90 | 3.10 | 3.30 |
3.00 | 3.20 | 3.40 | 3.60 |
3.25 | 3.45 | 3.65 | 3.85 |
3.30 | 3.50 | 3.70 | 3.90 |
3.60 | 3.80 | 4.00 | 4.20 |
3.90 | 4.10 | 4.30 | 4.50 |
4.20 | 4.40 | 4.60 | 4.80 |
4.50 | 4.70 | 4.90 | 5.10 |
4.80 | 5.00 | 5.20 | 5.40 |
5.10 | 5.30 | 5.70 | |
5.40 | 5.60 | ||
5.70 | 5.90 | ||
6.00 | 6.20 | ||
6.30 | 6.50 | ||
6.60 | 6.80 | ||
6.90 | 7.10 | ||
7.20 | 7.40 | ||
7.70 | |||
8.00 |
<tbody>
</tbody>
Can anyone help me with this problem? I am looking for a VBA solution for this problem.
Many thanks in advance,
Martin