Help with this challenging VBA coding

[student]

Friends,
I am writing to see whether you could please help with this VBA
coding.
The data below contains 10 SEC of totaling 500 obervations. We need to
choose 50:

For each SEC, choosing the five lowest DEBT among positive DEBT values
within this SEC, pulling together all these 50 observations,
then output containing PER, SIZE, SEC, DEBT, in ascending order of
DEBT.

Many many thanks.



CODE PER SIZE SEC DEBT
1 1000 298.84 A 101.94
2 1001 163.11 B 393.87
3 1002 267.79 C 115.78
4 1003 274.77 D 54.43
5 1004 269.03 E 126.18
6 1005 308.43 E 324.70
7 1006 365.95 E -55.93
8 1007 375.06 F 365.66
9 1008 548.37 B 110.42
10 1009 1215.41 E 137.37
11 1010 1264.68 D 116.36
12 1011 138.31 E 176.05
13 1012 139.72 G 139.89
14 1013 95.28 G 109.76
15 1014 76.46 F 190.52
16 1015 47.51 G 118.03
17 1016 56.07 B 353.27
18 1017 48.3 A 285.18
19 1018 40.06 D 51.41
20 1019 16.45 H 190.40
21 1020 16.13 E 164.52
22 1021 23.75 I 94.52
23 1022 22.23 C 768.47
24 1023 30.7 D 74.27
25 1024 29.63 F 123.65
26 1025 31.79 F 144.17
27 1026 31.72 D 135.14
28 1027 29.6 D 85.30
29 1028 33.54 A 145.79
30 1029 111.16 D 159.60
31 1030 110.61 B 138.36
32 1031 112.56 B 146.08
33 1032 109.67 J 67.19
34 1033 611.07 E 150.62
35 1034 646.16 I 139.63
36 1035 665.18 D 135.35
37 1036 677.48 J 76.04
38 1037 684.85 D -275.57
39 1038 696.98 C 147.17
40 1039 558.91 E 252.82
41 1040 1014.82 B 206.97
42 1041 1048.73 E 105.28
43 1042 253580.42 I 95.79
44 1043 663114 D 486.43
45 1044 812301.66 G 150.91
46 1045 124.98 D 68.32
47 1046 231.04 H 140.85
48 1047 398.7 E 235.23
49 1048 453.53 E 171.05
50 1049 409.84 C 99.98
51 1050 251.93 C 104.70
52 1051 220.43 D 391.17
53 1052 181.44 E 221.39
54 1053 3.66 A 121.57
55 1054 5050.37 I 201.77
56 1055 4157 J 75.16
57 1056 3856 G 105.73
58 1057 4331 D 72.23
59 1058 4307 D 132.85
60 1059 5360.21 B 202.23
61 1060 942.7 B 112.97
62 1061 1038.7 B 1180.42
63 1062 1075.2 B 165.40
64 1063 1407.2 D 101.60
65 1064 1510.7 D 63.11
66 1065 1451.7 B 170.06
67 1066 1241.4 C 130.04
68 1067 6392.77 G 127.38
69 1068 6028.89 C 114.48
70 1069 5388.44 C 184.11
71 1070 3628.7 B 528.44
72 1071 4479.38 B 187.18
73 1072 749.13 J 71.28
74 1073 649.73 C 92.79
75 1074 465.5 C 148.72
76 1075 5266.19 E 218.14
77 1076 8464.42 A 218.59
78 1077 7093.24 D 61.23
79 1078 4641.86 B -36.76
80 1079 1104.52 B 260.13
81 1080 969 E 357.24
82 1081 708.04 I 139.82
83 1082 916.75 C 97.26
84 1083 11868.8 A 124.05
85 1084 9872.12 A 222.63
86 1085 11577 E 817.61
87 1086 7757.51 I 122.35
88 1087 10053.2 D 81.96
89 1088 434.03 B 211.76
90 1089 356.48 C 117.52
91 1090 225.61 A 98.84
92 1091 1237.78 D 195.62
93 1092 1571.41 C 126.69
94 1093 1278.7 B 1383.10
95 1094 841.19 F 85.00
96 1095 975.27 C -3278.81
97 1096 468.75 H 101.23
98 1097 421.94 J 64.23
99 1098 294.14 J 67.13
100 1099 727.82 D 280.61
101 1100 721.91 D 59.52
102 1101 524.99 E 646.36
103 1102 11950.71 B 109.49
104 1103 12612.79 D 93.04
105 1104 11445.07 A 125.64
106 1105 7077.85 C -1815.91
107 1106 3317.76 E 160.10
108 1107 3179.91 D 81.59
109 1108 2972.39 D 87.55
110 1109 2727.23 H 123.52
111 1110 1915.44 E 217.02
112 1111 2428.82 C 185.97
113 1112 3979.65 I 144.97
114 1113 3539.78 F -268.99
115 1114 4224.23 C 217.64
116 1115 4085.31 I 160.39
117 1116 46837.59 I -65.96
118 1117 40331.45 E 289.48
119 1118 4.61 I 156.55
120 1119 2.12 C 222.11
121 1120 2.12 D 88.01
122 1121 12.24 D 146.28
123 1122 11.77 D 131.83
124 1123 11.74 E 192.62
125 1124 18.57 E 171.16
126 1125 18.88 I 160.08
127 1126 19.6 J 54.86
128 1127 22.29 J 151.12
129 1128 27.43 F 103.12
130 1129 28.1 I 115.21
131 1130 31.6 F 134.38
132 1131 36.66 E 138.51
133 1132 41.85 A 130.91
134 1133 42.44 E 136.69
135 1134 43.26 I 164.32
136 1135 50.55 D 65.51
137 1136 61.26 B 113.64
138 1137 56.86 A 103.73
139 1138 61.34 A 86.04
140 1139 61.34 I 153.71
141 1140 218.7 C 67.71
142 1141 218.51 A 132.53
143 1142 244.95 C 128.20
144 1143 350.08 I 101.89
145 1144 369.6 A 118.72
146 1145 503.85 E 157.29
147 1146 556.86 D 238.85
148 1147 577.12 J 86.04
149 1148 592.37 C 86.36
150 1149 665.81 C 129.82
151 1150 1076.9 C 109.22
152 1151 1076.9 C 333.45
153 1152 355 F 139.68
154 1153 342.74 A 148.12
155 1154 342.74 A 113.49
156 1155 1.95 G 84.99
157 1156 2.55 C 258.17
158 1157 1.38 F 145.74
159 1158 1.07 G 100.49
160 1159 0.73 F 88.91
161 1160 0.73 F -87.51
162 1161 2.5 D 105.44
163 1162 1.45 G 97.30
164 1163 2.48 C -103.58
165 1164 3.25 A 101.04
166 1165 3.25 E 242.59
167 1166 35.35 G 192.47
168 1167 56.22 F 105.07
169 1168 56.22 J 502.31
170 1169 403.21 E 1325.35
171 1170 423.98 E 160.11
172 1171 437.8 H 89.10
173 1172 472.91 E 197.44
174 1173 509.78 A 135.46
175 1174 547.42 J 73.46
176 1175 595.13 F 136.93
177 1176 595.13 J 116.90
178 1177 7.85 A 128.94
179 1178 10.44 D 1493.66
180 1179 11.05 C 132.75
181 1180 13.18 F 190.23
182 1181 17.79 B 188.62
183 1182 34.15 J 81.25
184 1183 100.19 C 148.15
185 1184 89.99 D 72.35
186 1185 89.778 D 159.45
187 1186 89.778 C 127.70
188 1187 5.468 D 138.23
189 1188 5.962 A 117.33
190 1189 7.881 E 252.12
191 1190 6.991 D 134.21
192 1191 9.335 E 209.16
193 1192 14.157 D 107.12
194 1193 25.009 F 114.48
195 1194 46.685 E 158.16
196 1195 86.294 A 236.05
197 1196 86.294 C -9.28
198 1197 86.294 B 240.43
199 1198 4.503 C 105.94
200 1199 2.155 F 145.45
201 1200 3.29 D 145.36
202 1201 4.253 G 65.31
203 1202 6.228 C 82.57
204 1203 6.13 C 144.33
205 1204 5.644 A 125.06
206 1205 5.833 A 124.90
207 1206 11.169 I 126.97
208 1207 9.79 C -48.76
209 1208 17.668 C 123.39
210 1209 13.482 D 86.18
211 1210 12.24 B 2732.34
212 1211 10.279 B -247.22
213 1212 14.124 D 69.00
214 1213 21.763 A 132.24
215 1214 21.341 C -83.21
216 1215 25.753 E 295.87
217 1216 13.088 A 129.50
218 1217 3.496 J 105.52
219 1218 1.902 C 107.86
220 1219 17.293 C 192.91
221 1220 24.63 C 208.40
222 1221 26.3 D 75.95
223 1222 25.8 C 131.31
224 1223 29.8 I 133.86
225 1224 28.9 G 57.77
226 1225 27.8 I 156.74
227 1226 27.2 J 73.81
228 1227 40.7 E 131.32
229 1228 46.335 C 172.16
230 1229 44.138 C 100.59
231 1230 39.7 A 147.05
232 1231 39.85 B 329.41
233 1232 43.56 D 274.64
234 1233 38.835 D 165.61
235 1234 35.391 B 145.57
236 1235 35.391 D 79.73
237 1236 35.391 C 360.90
238 1237 35.391 A 114.93
239 1238 35.391 B 156.13
240 1239 35.391 A 107.41
241 1240 35.391 F 92.21
242 1241 35.391 E 163.46
243 1242 35.391 C -796.65
244 1243 35.391 E 114.54
245 1244 35.391 G 132.83
246 1245 35.391 C 190.55
247 1246 35.391 G 41.17
248 1247 35.391 E 164.12
249 1248 35.391 A 157.34
250 1249 35.391 E 433.17
251 1250 35.391 D 275.99
252 1251 35.391 E -317.09
253 1252 35.391 B 120.84
254 1253 35.391 C 128.46
255 1254 35.391 C -174.06
256 1255 35.391 D 79.15
257 1256 35.391 E -92.46
258 1257 35.391 C 72.69
259 1258 35.391 I 137.59
260 1259 35.39 D 76.65
261 1260 35.39 F 112.56
262 1261 35.39 I 131.09
263 1262 35.39 D 219.47
264 1263 35.39 B 98.64
265 1264 35.39 E 145.04
266 1265 35.39 C 144.67
267 1266 35.39 I 136.57
268 1267 35.39 I 131.78
269 1268 35.39 A 146.12
270 1269 35.39 B 145.47
271 1270 35.39 I 162.79
272 1271 14.08 D 149.56
273 1272 16.27 C 102.41
274 1273 39.5 D 67.32
275 1274 39.5 C 121.38
276 1275 1113.06 E 101.17
277 1276 998.4 B 184.86
278 1277 644.46 C 111.93
279 1278 367.48 D 91.37
280 1279 372.79 E 170.16
281 1280 343.26 C 106.91
282 1281 263.18 A 107.45
283 1282 279.09 D 83.57
284 1283 278.95 C 112.95
285 1284 278.95 E 125.21
286 1285 278.95 D 102.14
287 1286 278.95 B 197.87
288 1287 278.95 J 60.70
289 1288 278.95 C 170.61
290 1289 278.95 D 151.42
291 1290 278.95 D 98.60
292 1291 249.24 A 202.80
293 1292 253.9 C 124.94
294 1293 3.29 E 142.80
295 1294 2.8 D 73.61
296 1295 1.7 I 142.04
297 1296 2.22 C 137.09
298 1297 6.07 C 176.56
299 1298 6.21 B 131.70
300 1299 6.18 G 578.78
301 1300 13.85 B 179.32
302 1301 13.52 B 577.10
303 1302 12.91 B 147.74
304 1303 20.48 D 123.44
305 1304 23.4 E 210.25
306 1305 28.51 B 166.95
307 1306 33.65 C 127.81
308 1307 40.41 D 59.88
309 1308 42.14 D 106.72
310 1309 43.33 D 74.44
311 1310 49.05 E 284.76
312 1311 51.23 E 148.63
313 1312 58.4 B 301.75
314 1313 60.71 E 133.53
315 1314 65.83 I 130.16
316 1315 190.76 G 273.87
317 1316 151.15 A 258.13
318 1317 148.89 D 169.26
319 1318 1330.75 D 69.05
320 1319 1877.96 E 125.85
321 1320 2074.92 J 117.31
322 1321 2386.79 B 132.71
323 1322 2438.73 J 64.90
324 1323 3049.35 D 66.81
325 1324 3159.41 J 133.97
326 1325 3159.41 E 147.19
327 1326 1.84 E 174.11
328 1327 2.01 E 272.56
329 1328 1.97 C -42.04
330 1329 2.09 C 145.54
331 1330 2.09 G 177.94
332 1331 2.14 C 145.41
333 1332 2.3 F 108.64
334 1333 2.172 C 140.68
335 1334 2.261 F 117.64
336 1335 2.261 J 101.20
337 1336 15.676 C 129.44
338 1337 1.654 A 107.81
339 1338 2.303 D 140.08
340 1339 1.936 A 111.20
341 1340 1.256 E -1767.32
342 1341 1.168 E 117.56
343 1342 1.334 G 79.09
344 1343 1.068 E 170.18
345 1344 31.729 J 70.45
346 1345 37.046 C 125.91
347 1346 46.221 C 134.70
348 1347 65.573 C 136.51
349 1348 73.365 E 172.89
350 1349 79.298 A 97.00
351 1350 84.393 G 110.20
352 1351 88.992 A 178.47
353 1352 118.496 A 100.77
354 1353 143.287 B 167.36
355 1354 641.678 E 201.41
356 1355 613.083 J 172.01
357 1356 596.198 C 106.99
358 1357 514.991 I 107.34
359 1358 471.69 I 133.13
360 1359 438.04 B 199.71
361 1360 469.031 B 128.54
362 1361 456.364 F 112.45
363 1362 456.364 F 97.16
364 1363 23.286 A 124.04
365 1364 53.765 D 188.94
366 1365 62.322 E -108.90
367 1366 66.594 D 54.57
368 1367 75.132 C 172.20
369 1368 104.412 G 118.48
370 1369 112.447 F 143.60
371 1370 121.494 G 145.17
372 1371 127.366 D 165.79
373 1372 123.309 D 114.51
374 1373 123.128 I 139.44
375 1374 136.827 F 125.32
376 1375 213.261 D 75.13
377 1376 213.68 D 145.16
378 1377 220.039 D 95.41
379 1378 224.481 F 171.31
380 1379 220.036 D -60681.00
381 1380 236.683 C 136.05
382 1381 236.683 E 175.16
383 1382 11.428 E 184.30
384 1383 16.597 B 112.28
385 1384 20.617 F 136.88
386 1385 22.154 H 178.89
387 1386 22.154 C 293.00
388 1387 1.861 A 145.55
389 1388 3.367 D 87.94
390 1389 2.552 I 109.83
391 1390 3.741 A 136.14
392 1391 3.49 A 138.55
393 1392 2.433 A 149.73
394 1393 1.879 G 106.02
395 1394 3.133 J 86.07
396 1395 3.557 A 87.88
397 1396 3.926 D 55.91
398 1397 5.299 I 114.60
399 1398 6.775 E 347.38
400 1399 6.755 E -161.36
401 1400 12.465 I 449.49
402 1401 13.413 B 264.26
403 1402 11.4 J 149.99
404 1403 10.545 D 211.59
405 1404 11.072 G 102.57
406 1405 11.072 C 123.54
407 1406 24.44 F 98.52
408 1407 42.059 C 106.40
409 1408 47.706 G 135.85
410 1409 60.695 D 337.38
411 1410 60.695 D 144.36
412 1411 8.529 J 135.87
413 1412 8.241 C 183.30
414 1413 13.99 E 209.56
415 1414 14.586 F 110.47
416 1415 16.042 A 144.81
417 1416 16.28 D 3151.34
418 1417 21.592 J NA
419 1418 27.533 H 774.62
420 1419 33.28 B 184.36
421 1420 36.464 C 109.64
422 1421 43.419 C 123.12
423 1422 54.529 C 232.54
424 1423 62.689 C 89.66
425 1424 95.149 D 136.14
426 1425 122.021 B 212.26
427 1426 115.088 E -691.57
428 1427 107.374 J 63.88
429 1428 121.817 D 103.48
430 1429 143.665 I 135.71
431 1430 229.636 E 322.70
432 1431 515.608 D 115.54
433 1432 602.525 I 145.81
434 1433 583.945 C 125.97
435 1434 553.054 F 94.49
436 1435 553.054 E 184.89
437 1436 33.233 E 195.37
438 1437 31.387 G 107.05
439 1438 35.837 B -28.73
440 1439 50.651 E 149.86
441 1440 76.137 A 134.29
442 1441 113.567 E 136.22
443 1442 128.121 A 122.89
444 1443 149.782 B 362.93
445 1444 188.712 C 186.61
446 1445 227.729 C 114.09
447 1446 246.015 C 111.55
448 1447 236.062 D 87.70
449 1448 250.858 J 115.04
450 1449 226.09 D 58.39
451 1450 220.116 C 99.23
452 1451 186.416 F 144.26
453 1452 170.405 A 120.31
454 1453 3.424 I 2518.77
455 1454 2.959 D 88.81
456 1455 1.979 A 131.33
457 1456 1.434 E -77.27
458 1457 0.804 A 129.09
459 1458 0.168 G 64.69
460 1459 0 A 123.28
461 1460 0.001 B 119.02
462 1461 389.46 D 120.77
463 1462 426.81 I 117.53
464 1463 487.137 J 53.31
465 1464 495.793 B 138.00
466 1465 503.467 E 116.91
467 1466 503.262 H 151.35
468 1467 516.646 C 128.19
469 1468 527.971 C 147.84
470 1469 532.559 D 236.44
471 1470 542.478 G 158.19
472 1471 518.963 D 76.54
473 1472 518.963 I 108.30
474 1473 45.752 I 156.91
475 1474 45.251 D 94.10
476 1475 41.009 A 118.63
477 1476 8.567 B 178.53
478 1477 5.912 B -48.66
479 1478 5.912 J 129.27
480 1479 2.406 B 108.44
481 1480 2.141 D 94.84
482 1481 1.756 C 824.49
483 1482 1.432 E 131.22
484 1483 1.374 G 155.66
485 1484 2.148 C 120.73
486 1485 2.101 I 203.40
487 1486 2.216 J 377.78
488 1487 2.91 H 111.91
489 1488 2.47 I 196.69
490 1489 2.486 B 121.79
491 1490 5.654 C 142.49
492 1491 11.408 F 117.88
493 1492 11.538 E 99.18
494 1493 17.814 E 185.67
495 1494 24.58 D 54.53
496 1495 54.939 J 81.09
497 1496 37.749 E 353.79
498 1497 37.749 C 145.30
499 1498 2.907 B 165.85
500 1499 3.552 D 98.47

 

[OssieMac]

I feel that this is probably a student assignment and as such I am reluctant
to give you code that you can simply copy. However, I will give you some
pointers to get started and perhaps others will give you some more or better
ones.

Copy the data to a new location (new worksheet)so that you do not loose the
original. Work with the copied data. It can be deleted later.

Sort on the SEC and DEBT column.

Find A in SEC column and then use find next within a loop to test each find
is > zero and copy each valid line of data to another new area. Establish a
counter in the loop so that you know when you have the required 5. Repeat for
remaining SEC.

The procedure could be done with a loop within a loop. The first loop to
incremnent the SEC by commencing with A. Use ascii code to increment. (ascii
65 is A.) and incrementing it until J is reached. The second loop within
would be the previous paragraph.

Regards,

OssieMac

 

[Kiddio]

like what OssieMac wrote
find all the positive value with the same sec than copy them to a new page
after which sort them in the order using sort, the first five it the one
with the lowest dept.

I also wont give u the code lol so read yourself from the link below

find method :
http://msdn2.microsoft.com/de-de/library/microsoft.office.interop.excel.range.find(VS.80).aspx

findnext method :
http://msdn2.microsoft.com/de-de/library/microsoft.office.interop.excel.range.findnext(VS.80).aspx

sort method :
http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.namedrange.sort(VS.80).aspx

Best Regards
Kiddio

 

[ilia]

I probably wouldn't use VBA for this. But, you can do the following
programmatically, if this list gets updated:

1. Make the following criteria range for each section you have
SEC
="=A"
2. Use Advanced Filter and the "Copy to New Location" option.
3. Repeat for each section
4. Sort each range
5. Use top n values that you need.
6. Concatenate ranges if desired.