Hi forum gurus,
I'm a novice at VBA, having started to learn it on Friday! I have 50 spreadsheets formatted the same as I'm working on now, and I've been writing code to automate the process. There's one step I can't seem to find answers for on the net.
Essentially I needed to group data when the depth jumps (which I've managed no probs, see column B). I also need to subdivide any groups more than 40 cells in a row into equal portions - I can't figure this bit out.
In the columns below, I have two examples, highlighted in column M. Group 1 has 7 cells in it, group 2 has 50 cells in it. Each group is separated by a blank row. The number of groups varies per spreadsheet, as does the length of each group. If the group is <40 continuous cells (e.g. Group 1), then it's to remain as is. If it's more than 40 cells (e.g. Group 2), split these in half by inserting a blank row after the midpoint. So, for group 2 which runs from M10-M59, insert a new row at M34 (or M35, not fussed).
To complicate things, if there are more than 80 cells, it would have to be subdivided into 3 equal sections; more than 120 cells into 4 sections etc. I've written a quick Mround formula which defines the number of segments each group would have to be divided into in column O. So Group 1 would need to be subdivided 0 times, Group 2 subdivided 1 time.
Any ideas? I'm really bogged down on this.
Thanks in advance, Hugo
<tbody>
</tbody>
I'm a novice at VBA, having started to learn it on Friday! I have 50 spreadsheets formatted the same as I'm working on now, and I've been writing code to automate the process. There's one step I can't seem to find answers for on the net.
Essentially I needed to group data when the depth jumps (which I've managed no probs, see column B). I also need to subdivide any groups more than 40 cells in a row into equal portions - I can't figure this bit out.
In the columns below, I have two examples, highlighted in column M. Group 1 has 7 cells in it, group 2 has 50 cells in it. Each group is separated by a blank row. The number of groups varies per spreadsheet, as does the length of each group. If the group is <40 continuous cells (e.g. Group 1), then it's to remain as is. If it's more than 40 cells (e.g. Group 2), split these in half by inserting a blank row after the midpoint. So, for group 2 which runs from M10-M59, insert a new row at M34 (or M35, not fussed).
To complicate things, if there are more than 80 cells, it would have to be subdivided into 3 equal sections; more than 120 cells into 4 sections etc. I've written a quick Mround formula which defines the number of segments each group would have to be divided into in column O. So Group 1 would need to be subdivided 0 times, Group 2 subdivided 1 time.
Any ideas? I'm really bogged down on this.
Thanks in advance, Hugo
B | L | M | O | |
1 | DEPTH (M) | depth diff | Countgroup | Mround |
2 | 331.0128 | 0.1524 | 7 | 0 |
3 | 331.1652 | 0.1524 | 7 | 0 |
4 | 331.3176 | 0.1524 | 7 | 0 |
5 | 331.47 | 0.1524 | 7 | 0 |
6 | 331.6224 | 0.1524 | 7 | 0 |
7 | 331.7748 | 0.3048 | 7 | 0 |
8 | 332.0796 | 12.0396 | 7 | 0 |
9 | ||||
10 | 485.2416 | 0.1524 | 50 | 1 |
11 | 485.394 | 0.1524 | 50 | 1 |
12 | 485.5464 | 0.1524 | 50 | 1 |
13 | 485.6988 | 0.1524 | 50 | 1 |
14 | 485.8512 | 0.1524 | 50 | 1 |
15 | 486.0036 | 0.1524 | 50 | 1 |
16 | 486.156 | 0.1524 | 50 | 1 |
17 | 486.3084 | 0.1524 | 50 | 1 |
18 | 486.4608 | 0.1524 | 50 | 1 |
19 | 486.6132 | 0.1524 | 50 | 1 |
20 | 486.7656 | 0.1524 | 50 | 1 |
21 | 486.918 | 0.1524 | 50 | 1 |
22 | 487.0704 | 2.7432 | 50 | 1 |
23 | 489.8136 | 0.1524 | 50 | 1 |
24 | 489.966 | 0.1524 | 50 | 1 |
25 | 490.1184 | 0.1524 | 50 | 1 |
<tbody>
</tbody>
Last edited: