Espejo y actualizar celdas a través de pestañas y formas -- vba campo con excel camp codereview Relacionados El problema

Mirror and update cells across tabs and forms


5
vote

problema

Español

De las solicitudes a continuación. He intentado reescribir mi solicitud. Estoy totalmente de acuerdo en que sería más fácil para nuestra empresa proporcionarnos el software de la mesa de ayuda. Desafortunadamente, mi departamento tiene la tarea para sentarse entre nuestros usuarios finales y tres escritorios de ayuda separados que se ocupan de diferentes problemas. No se nos permite ingresar en boletos de escritorio de ayuda hasta que hayamos determinado en cuál de los sistemas de la mesa de ayuda pertenece el problema, y ​​muchos de los problemas que manejamos nunca se llevan a la mesa de ayuda porque podemos resolverlos a través de la educación. Nuestro gerente de departamento ha estado trabajando para los años que intentan acceder a una mesa de ayuda sin resultados, por lo que en lugar de esperar a que la compañía hiciera algo que he estado trabajando para construir este archivo para darle a nuestro equipo algo que podemos usar.

He tomado una copia de mi archivo y lo manipulé en un archivo de prueba que contiene cada célula que se ve afectada por el código VBA, pero también cumple con las políticas de seguridad de datos de nuestra empresa. He incluido capturas de pantalla y las piezas de código problemáticas a continuación. Por favor, hágamelo saber si hay más información que pueda proporcionar sobre el archivo.

Aquí hay una imagen de la pantalla de la lista de prioridades:

ingrese la descripción de la imagen aquí

y aquí hay una imagen de la tabla donde se almacenan los problemas:

ingrese la descripción de la imagen aquí

El problema con el código que me causa la mayor cantidad de problemas D / T Bulk es el código que permite a los usuarios actualizar libremente los problemas desde la primera pantalla, y tener esa información actualizada directamente en la segunda pantalla. El código funciona completamente, pero es muy "gordo", por lo que cualquier ayuda para adelgazarla se apreciaría enormemente.

El código para ese proceso es:

  Private Sub Worksheet_Change(ByVal Target As Range)  Set r1 = Sheets("Priority Table").Range("B2") Set r2 = Sheets("Priority Table").Range("I2") Set r3 = Sheets("Priority Table").Range("P2") Set r4 = Sheets("Priority Table").Range("B13") Set r5 = Sheets("Priority Table").Range("I13") Set r6 = Sheets("Priority Table").Range("P13") Set r7 = Sheets("Priority Table").Range("B24") Set r8 = Sheets("Priority Table").Range("I24") Set r9 = Sheets("Priority Table").Range("P24") Set r10 = Sheets("Priority Table").Range("B35") Set r11 = Sheets("Priority Table").Range("B4") Set r12 = Sheets("Priority Table").Range("I4") Set r13 = Sheets("Priority Table").Range("P4") Set r14 = Sheets("Priority Table").Range("B15") Set r15 = Sheets("Priority Table").Range("I15") Set r16 = Sheets("Priority Table").Range("P15") Set r17 = Sheets("Priority Table").Range("B26") Set r18 = Sheets("Priority Table").Range("I26") Set r19 = Sheets("Priority Table").Range("P26") Set r20 = Sheets("Priority Table").Range("B37") Set r21 = Sheets("Priority Table").Range("D4") Set r22 = Sheets("Priority Table").Range("K4") Set r23 = Sheets("Priority Table").Range("R4") Set r24 = Sheets("Priority Table").Range("D15") Set r25 = Sheets("Priority Table").Range("K15") Set r26 = Sheets("Priority Table").Range("R15") Set r27 = Sheets("Priority Table").Range("D26") Set r28 = Sheets("Priority Table").Range("K26") Set r29 = Sheets("Priority Table").Range("R26") Set r30 = Sheets("Priority Table").Range("D37") Set r31 = Sheets("Priority Table").Range("C8") Set r32 = Sheets("Priority Table").Range("J8") Set r33 = Sheets("Priority Table").Range("Q8") Set r34 = Sheets("Priority Table").Range("C19") Set r35 = Sheets("Priority Table").Range("J19") Set r36 = Sheets("Priority Table").Range("Q19") Set r37 = Sheets("Priority Table").Range("C30") Set r38 = Sheets("Priority Table").Range("J30") Set r39 = Sheets("Priority Table").Range("Q30") Set r40 = Sheets("Priority Table").Range("C41") Set r41 = Sheets("Priority Table").Range("C9") Set r42 = Sheets("Priority Table").Range("J9") Set r43 = Sheets("Priority Table").Range("Q9") Set r44 = Sheets("Priority Table").Range("C20") Set r45 = Sheets("Priority Table").Range("J20") Set r46 = Sheets("Priority Table").Range("Q20") Set r47 = Sheets("Priority Table").Range("C31") Set r48 = Sheets("Priority Table").Range("J31") Set r49 = Sheets("Priority Table").Range("Q31") Set r50 = Sheets("Priority Table").Range("C42") Set r51 = Sheets("Priority Table").Range("B11") Set r52 = Sheets("Priority Table").Range("I11") Set r53 = Sheets("Priority Table").Range("P11") Set r54 = Sheets("Priority Table").Range("B22") Set r55 = Sheets("Priority Table").Range("I22") Set r56 = Sheets("Priority Table").Range("P22") Set r57 = Sheets("Priority Table").Range("B33") Set r58 = Sheets("Priority Table").Range("I33") Set r59 = Sheets("Priority Table").Range("P33") Set r60 = Sheets("Priority Table").Range("B44") Set r61 = Sheets("Priority Table").Range("D6") Set r62 = Sheets("Priority Table").Range("K6") Set r63 = Sheets("Priority Table").Range("R6") Set r64 = Sheets("Priority Table").Range("D17") Set r65 = Sheets("Priority Table").Range("K17") Set r66 = Sheets("Priority Table").Range("R17") Set r67 = Sheets("Priority Table").Range("D28") Set r68 = Sheets("Priority Table").Range("K28") Set r69 = Sheets("Priority Table").Range("R28") Set r70 = Sheets("Priority Table").Range("D39") Set r71 = Sheets("Priority Table").Range("D7") Set r72 = Sheets("Priority Table").Range("K7") Set r73 = Sheets("Priority Table").Range("R7") Set r74 = Sheets("Priority Table").Range("D18") Set r75 = Sheets("Priority Table").Range("K18") Set r76 = Sheets("Priority Table").Range("R18") Set r77 = Sheets("Priority Table").Range("D29") Set r78 = Sheets("Priority Table").Range("K29") Set r79 = Sheets("Priority Table").Range("R29") Set r80 = Sheets("Priority Table").Range("D40") Set r81 = Sheets("Priority Table").Range("D8") Set r82 = Sheets("Priority Table").Range("K8") Set r83 = Sheets("Priority Table").Range("R8") Set r84 = Sheets("Priority Table").Range("D19") Set r85 = Sheets("Priority Table").Range("K19") Set r86 = Sheets("Priority Table").Range("R19") Set r87 = Sheets("Priority Table").Range("D30") Set r88 = Sheets("Priority Table").Range("K30") Set r89 = Sheets("Priority Table").Range("R30") Set r90 = Sheets("Priority Table").Range("D41") Set r91 = Sheets("Priority Table").Range("D9") Set r92 = Sheets("Priority Table").Range("K9") Set r93 = Sheets("Priority Table").Range("R9") Set r94 = Sheets("Priority Table").Range("D20") Set r95 = Sheets("Priority Table").Range("K20") Set r96 = Sheets("Priority Table").Range("R20") Set r97 = Sheets("Priority Table").Range("D31") Set r98 = Sheets("Priority Table").Range("K31") Set r99 = Sheets("Priority Table").Range("R31") Set r100 = Sheets("Priority Table").Range("D42") Set r101 = Sheets("Priority Table").Range("D10") Set r102 = Sheets("Priority Table").Range("K10") Set r103 = Sheets("Priority Table").Range("R10") Set r104 = Sheets("Priority Table").Range("D21") Set r105 = Sheets("Priority Table").Range("K21") Set r106 = Sheets("Priority Table").Range("R21") Set r107 = Sheets("Priority Table").Range("D32") Set r108 = Sheets("Priority Table").Range("K32") Set r109 = Sheets("Priority Table").Range("R32") Set r110 = Sheets("Priority Table").Range("D43") Set r111 = Sheets("Priority Table").Range("D11") Set r112 = Sheets("Priority Table").Range("K11") Set r113 = Sheets("Priority Table").Range("R11") Set r114 = Sheets("Priority Table").Range("D22") Set r115 = Sheets("Priority Table").Range("K22") Set r116 = Sheets("Priority Table").Range("R22") Set r117 = Sheets("Priority Table").Range("D33") Set r118 = Sheets("Priority Table").Range("K33") Set r119 = Sheets("Priority Table").Range("R33") Set r120 = Sheets("Priority Table").Range("D44") Set r121 = Sheets("Priority Table").Range("E4") Set r122 = Sheets("Priority Table").Range("L4") Set r123 = Sheets("Priority Table").Range("S4") Set r124 = Sheets("Priority Table").Range("E15") Set r125 = Sheets("Priority Table").Range("L15") Set r126 = Sheets("Priority Table").Range("S15") Set r127 = Sheets("Priority Table").Range("E26") Set r128 = Sheets("Priority Table").Range("L26") Set r129 = Sheets("Priority Table").Range("S26") Set r130 = Sheets("Priority Table").Range("E37") Set r131 = Sheets("Priority Table").Range("F4") Set r132 = Sheets("Priority Table").Range("M4") Set r133 = Sheets("Priority Table").Range("T4") Set r134 = Sheets("Priority Table").Range("F15") Set r135 = Sheets("Priority Table").Range("M15") Set r136 = Sheets("Priority Table").Range("S15") Set r137 = Sheets("Priority Table").Range("F26") Set r138 = Sheets("Priority Table").Range("M26") Set r139 = Sheets("Priority Table").Range("T26") Set r140 = Sheets("Priority Table").Range("F37") Set r141 = Sheets("Priority Table").Range("E6") Set r142 = Sheets("Priority Table").Range("L6") Set r143 = Sheets("Priority Table").Range("S6") Set r144 = Sheets("Priority Table").Range("E17") Set r145 = Sheets("Priority Table").Range("L17") Set r146 = Sheets("Priority Table").Range("S17") Set r147 = Sheets("Priority Table").Range("E28") Set r148 = Sheets("Priority Table").Range("L28") Set r149 = Sheets("Priority Table").Range("S28") Set r150 = Sheets("Priority Table").Range("E39") Set r151 = Sheets("Priority Table").Range("F6") Set r152 = Sheets("Priority Table").Range("M6") Set r153 = Sheets("Priority Table").Range("T6") Set r154 = Sheets("Priority Table").Range("F17") Set r155 = Sheets("Priority Table").Range("M17") Set r156 = Sheets("Priority Table").Range("T17") Set r157 = Sheets("Priority Table").Range("F28") Set r158 = Sheets("Priority Table").Range("M28") Set r159 = Sheets("Priority Table").Range("T28") Set r160 = Sheets("Priority Table").Range("F39") Set r161 = Sheets("Priority Table").Range("E8") Set r162 = Sheets("Priority Table").Range("L8") Set r163 = Sheets("Priority Table").Range("S8") Set r164 = Sheets("Priority Table").Range("E19") Set r165 = Sheets("Priority Table").Range("L19") Set r166 = Sheets("Priority Table").Range("S19") Set r167 = Sheets("Priority Table").Range("E30") Set r168 = Sheets("Priority Table").Range("L30") Set r169 = Sheets("Priority Table").Range("S30") Set r170 = Sheets("Priority Table").Range("E41") Set r171 = Sheets("Priority Table").Range("F8") Set r172 = Sheets("Priority Table").Range("M8") Set r173 = Sheets("Priority Table").Range("T8") Set r174 = Sheets("Priority Table").Range("F19") Set r175 = Sheets("Priority Table").Range("M19") Set r176 = Sheets("Priority Table").Range("T19") Set r177 = Sheets("Priority Table").Range("F30") Set r178 = Sheets("Priority Table").Range("M30") Set r179 = Sheets("Priority Table").Range("T30") Set r180 = Sheets("Priority Table").Range("F41") Set r181 = Sheets("Priority Table").Range("E10") Set r182 = Sheets("Priority Table").Range("L10") Set r183 = Sheets("Priority Table").Range("S10") Set r184 = Sheets("Priority Table").Range("E21") Set r185 = Sheets("Priority Table").Range("L21") Set r186 = Sheets("Priority Table").Range("S21") Set r187 = Sheets("Priority Table").Range("E32") Set r188 = Sheets("Priority Table").Range("L32") Set r189 = Sheets("Priority Table").Range("S32") Set r190 = Sheets("Priority Table").Range("E43") Set r191 = Sheets("Priority Table").Range("F10") Set r192 = Sheets("Priority Table").Range("M10") Set r193 = Sheets("Priority Table").Range("T10") Set r194 = Sheets("Priority Table").Range("F21") Set r195 = Sheets("Priority Table").Range("M21") Set r196 = Sheets("Priority Table").Range("T21") Set r197 = Sheets("Priority Table").Range("F32") Set r198 = Sheets("Priority Table").Range("M32") Set r199 = Sheets("Priority Table").Range("T32") Set r200 = Sheets("Priority Table").Range("F43") Set r201 = Sheets("Priority Table").Range("F11") Set r202 = Sheets("Priority Table").Range("M11") Set r203 = Sheets("Priority Table").Range("T11") Set r204 = Sheets("Priority Table").Range("F22") Set r205 = Sheets("Priority Table").Range("M22") Set r206 = Sheets("Priority Table").Range("T22") Set r207 = Sheets("Priority Table").Range("F33") Set r208 = Sheets("Priority Table").Range("M33") Set r209 = Sheets("Priority Table").Range("T33") Set r210 = Sheets("Priority Table").Range("F44") Set r211 = Sheets("Priority Table").Range("G6") Set r212 = Sheets("Priority Table").Range("N6") Set r213 = Sheets("Priority Table").Range("U6") Set r214 = Sheets("Priority Table").Range("G17") Set r215 = Sheets("Priority Table").Range("N17") Set r216 = Sheets("Priority Table").Range("U17") Set r217 = Sheets("Priority Table").Range("G28") Set r218 = Sheets("Priority Table").Range("N28") Set r219 = Sheets("Priority Table").Range("U28") Set r220 = Sheets("Priority Table").Range("G39") Set r221 = Sheets("Issue List").Range("B2") Set r222 = Sheets("Issue List").Range("B3") Set r223 = Sheets("Issue List").Range("B4") Set r224 = Sheets("Issue List").Range("B5") Set r225 = Sheets("Issue List").Range("B6") Set r226 = Sheets("Issue List").Range("B7") Set r227 = Sheets("Issue List").Range("B8") Set r228 = Sheets("Issue List").Range("B9") Set r229 = Sheets("Issue List").Range("B10") Set r230 = Sheets("Issue List").Range("B11") Set r231 = Sheets("Issue List").Range("C2") Set r232 = Sheets("Issue List").Range("C3") Set r233 = Sheets("Issue List").Range("C4") Set r234 = Sheets("Issue List").Range("C5") Set r235 = Sheets("Issue List").Range("C6") Set r236 = Sheets("Issue List").Range("C7") Set r237 = Sheets("Issue List").Range("C8") Set r238 = Sheets("Issue List").Range("C9") Set r239 = Sheets("Issue List").Range("C10") Set r240 = Sheets("Issue List").Range("C11") Set r241 = Sheets("Issue List").Range("D2") Set r242 = Sheets("Issue List").Range("D3") Set r243 = Sheets("Issue List").Range("D4") Set r244 = Sheets("Issue List").Range("D5") Set r245 = Sheets("Issue List").Range("D6") Set r246 = Sheets("Issue List").Range("D7") Set r247 = Sheets("Issue List").Range("D8") Set r248 = Sheets("Issue List").Range("D9") Set r249 = Sheets("Issue List").Range("D10") Set r250 = Sheets("Issue List").Range("D11") Set r251 = Sheets("Issue List").Range("E2") Set r252 = Sheets("Issue List").Range("E3") Set r253 = Sheets("Issue List").Range("E4") Set r254 = Sheets("Issue List").Range("E5") Set r255 = Sheets("Issue List").Range("E6") Set r256 = Sheets("Issue List").Range("E7") Set r257 = Sheets("Issue List").Range("E8") Set r258 = Sheets("Issue List").Range("E9") Set r259 = Sheets("Issue List").Range("E10") Set r260 = Sheets("Issue List").Range("E11") Set r261 = Sheets("Issue List").Range("F2") Set r262 = Sheets("Issue List").Range("F3") Set r263 = Sheets("Issue List").Range("F4") Set r264 = Sheets("Issue List").Range("F5") Set r265 = Sheets("Issue List").Range("F6") Set r266 = Sheets("Issue List").Range("F7") Set r267 = Sheets("Issue List").Range("F8") Set r268 = Sheets("Issue List").Range("F9") Set r269 = Sheets("Issue List").Range("F10") Set r270 = Sheets("Issue List").Range("F11") Set r271 = Sheets("Issue List").Range("G2") Set r272 = Sheets("Issue List").Range("G3") Set r273 = Sheets("Issue List").Range("G4") Set r274 = Sheets("Issue List").Range("G5") Set r275 = Sheets("Issue List").Range("G6") Set r276 = Sheets("Issue List").Range("G7") Set r277 = Sheets("Issue List").Range("G8") Set r278 = Sheets("Issue List").Range("G9") Set r279 = Sheets("Issue List").Range("G10") Set r280 = Sheets("Issue List").Range("G11") Set r281 = Sheets("Issue List").Range("H2") Set r282 = Sheets("Issue List").Range("H3") Set r283 = Sheets("Issue List").Range("H4") Set r284 = Sheets("Issue List").Range("H5") Set r285 = Sheets("Issue List").Range("H6") Set r286 = Sheets("Issue List").Range("H7") Set r287 = Sheets("Issue List").Range("H8") Set r288 = Sheets("Issue List").Range("H9") Set r289 = Sheets("Issue List").Range("H10") Set r290 = Sheets("Issue List").Range("H11") Set r291 = Sheets("Issue List").Range("I2") Set r292 = Sheets("Issue List").Range("I3") Set r293 = Sheets("Issue List").Range("I4") Set r294 = Sheets("Issue List").Range("I5") Set r295 = Sheets("Issue List").Range("I6") Set r296 = Sheets("Issue List").Range("I7") Set r297 = Sheets("Issue List").Range("I8") Set r298 = Sheets("Issue List").Range("I9") Set r299 = Sheets("Issue List").Range("I10") Set r300 = Sheets("Issue List").Range("I11") Set r301 = Sheets("Issue List").Range("J2") Set r302 = Sheets("Issue List").Range("J3") Set r303 = Sheets("Issue List").Range("J4") Set r304 = Sheets("Issue List").Range("J5") Set r305 = Sheets("Issue List").Range("J6") Set r306 = Sheets("Issue List").Range("J7") Set r307 = Sheets("Issue List").Range("J8") Set r308 = Sheets("Issue List").Range("J9") Set r309 = Sheets("Issue List").Range("J10") Set r310 = Sheets("Issue List").Range("J11") Set r311 = Sheets("Issue List").Range("K2") Set r312 = Sheets("Issue List").Range("K3") Set r313 = Sheets("Issue List").Range("K4") Set r314 = Sheets("Issue List").Range("K5") Set r315 = Sheets("Issue List").Range("K6") Set r316 = Sheets("Issue List").Range("K7") Set r317 = Sheets("Issue List").Range("K8") Set r318 = Sheets("Issue List").Range("K9") Set r319 = Sheets("Issue List").Range("K10") Set r320 = Sheets("Issue List").Range("K11") Set r321 = Sheets("Issue List").Range("L2") Set r322 = Sheets("Issue List").Range("L3") Set r323 = Sheets("Issue List").Range("L4") Set r324 = Sheets("Issue List").Range("L5") Set r325 = Sheets("Issue List").Range("L6") Set r326 = Sheets("Issue List").Range("L7") Set r327 = Sheets("Issue List").Range("L8") Set r328 = Sheets("Issue List").Range("L9") Set r329 = Sheets("Issue List").Range("L10") Set r330 = Sheets("Issue List").Range("L11") Set r331 = Sheets("Issue List").Range("M2") Set r332 = Sheets("Issue List").Range("M3") Set r333 = Sheets("Issue List").Range("M4") Set r334 = Sheets("Issue List").Range("M5") Set r335 = Sheets("Issue List").Range("M6") Set r336 = Sheets("Issue List").Range("M7") Set r337 = Sheets("Issue List").Range("M8") Set r338 = Sheets("Issue List").Range("M9") Set r339 = Sheets("Issue List").Range("M10") Set r340 = Sheets("Issue List").Range("M11") Set r341 = Sheets("Issue List").Range("P2") Set r342 = Sheets("Issue List").Range("P3") Set r343 = Sheets("Issue List").Range("P4") Set r344 = Sheets("Issue List").Range("P5") Set r345 = Sheets("Issue List").Range("P6") Set r346 = Sheets("Issue List").Range("P7") Set r347 = Sheets("Issue List").Range("P8") Set r348 = Sheets("Issue List").Range("P9") Set r349 = Sheets("Issue List").Range("P10") Set r350 = Sheets("Issue List").Range("P11") Set r351 = Sheets("Issue List").Range("Q2") Set r352 = Sheets("Issue List").Range("Q3") Set r353 = Sheets("Issue List").Range("Q4") Set r354 = Sheets("Issue List").Range("Q5") Set r355 = Sheets("Issue List").Range("Q6") Set r356 = Sheets("Issue List").Range("Q7") Set r357 = Sheets("Issue List").Range("Q8") Set r358 = Sheets("Issue List").Range("Q9") Set r359 = Sheets("Issue List").Range("Q10") Set r360 = Sheets("Issue List").Range("Q11") Set r361 = Sheets("Issue List").Range("R2") Set r362 = Sheets("Issue List").Range("R3") Set r363 = Sheets("Issue List").Range("R4") Set r364 = Sheets("Issue List").Range("R5") Set r365 = Sheets("Issue List").Range("R6") Set r366 = Sheets("Issue List").Range("R7") Set r367 = Sheets("Issue List").Range("R8") Set r368 = Sheets("Issue List").Range("R9") Set r369 = Sheets("Issue List").Range("R10") Set r370 = Sheets("Issue List").Range("R11") Set r371 = Sheets("Issue List").Range("S2") Set r372 = Sheets("Issue List").Range("S3") Set r373 = Sheets("Issue List").Range("S4") Set r374 = Sheets("Issue List").Range("S5") Set r375 = Sheets("Issue List").Range("S6") Set r376 = Sheets("Issue List").Range("S7") Set r377 = Sheets("Issue List").Range("S8") Set r378 = Sheets("Issue List").Range("S9") Set r379 = Sheets("Issue List").Range("S10") Set r380 = Sheets("Issue List").Range("S11") Set r381 = Sheets("Issue List").Range("T2") Set r382 = Sheets("Issue List").Range("T3") Set r383 = Sheets("Issue List").Range("T4") Set r384 = Sheets("Issue List").Range("T5") Set r385 = Sheets("Issue List").Range("T6") Set r386 = Sheets("Issue List").Range("T7") Set r387 = Sheets("Issue List").Range("T8") Set r388 = Sheets("Issue List").Range("T9") Set r389 = Sheets("Issue List").Range("T10") Set r390 = Sheets("Issue List").Range("T11") Set r391 = Sheets("Issue List").Range("U2") Set r392 = Sheets("Issue List").Range("U3") Set r393 = Sheets("Issue List").Range("U4") Set r394 = Sheets("Issue List").Range("U5") Set r395 = Sheets("Issue List").Range("U6") Set r396 = Sheets("Issue List").Range("U7") Set r397 = Sheets("Issue List").Range("U8") Set r398 = Sheets("Issue List").Range("U9") Set r399 = Sheets("Issue List").Range("U10") Set r400 = Sheets("Issue List").Range("U11") Set r401 = Sheets("Issue List").Range("V2") Set r402 = Sheets("Issue List").Range("V3") Set r403 = Sheets("Issue List").Range("V4") Set r404 = Sheets("Issue List").Range("V5") Set r405 = Sheets("Issue List").Range("V6") Set r406 = Sheets("Issue List").Range("V7") Set r407 = Sheets("Issue List").Range("V8") Set r408 = Sheets("Issue List").Range("V9") Set r409 = Sheets("Issue List").Range("V10") Set r410 = Sheets("Issue List").Range("V11") Set r411 = Sheets("Issue List").Range("W2") Set r412 = Sheets("Issue List").Range("W3") Set r413 = Sheets("Issue List").Range("W4") Set r414 = Sheets("Issue List").Range("W5") Set r415 = Sheets("Issue List").Range("W6") Set r416 = Sheets("Issue List").Range("W7") Set r417 = Sheets("Issue List").Range("W8") Set r418 = Sheets("Issue List").Range("W9") Set r419 = Sheets("Issue List").Range("W10") Set r420 = Sheets("Issue List").Range("W11") Set r421 = Sheets("Issue List").Range("X2") Set r422 = Sheets("Issue List").Range("X3") Set r423 = Sheets("Issue List").Range("X4") Set r424 = Sheets("Issue List").Range("X5") Set r425 = Sheets("Issue List").Range("X6") Set r426 = Sheets("Issue List").Range("X7") Set r427 = Sheets("Issue List").Range("X8") Set r428 = Sheets("Issue List").Range("X9") Set r429 = Sheets("Issue List").Range("X10") Set r430 = Sheets("Issue List").Range("X11") Set r431 = Sheets("Issue List").Range("Y2") Set r432 = Sheets("Issue List").Range("Y3") Set r433 = Sheets("Issue List").Range("Y4") Set r434 = Sheets("Issue List").Range("Y5") Set r435 = Sheets("Issue List").Range("Y6") Set r436 = Sheets("Issue List").Range("Y7") Set r437 = Sheets("Issue List").Range("Y8") Set r438 = Sheets("Issue List").Range("Y9") Set r439 = Sheets("Issue List").Range("Y10") Set r440 = Sheets("Issue List").Range("Y11") Set r441 = Sheets("Priority Table").Range("G4") Set r442 = Sheets("Priority Table").Range("N4") Set r443 = Sheets("Priority Table").Range("U4") Set r444 = Sheets("Priority Table").Range("G15") Set r445 = Sheets("Priority Table").Range("N15") Set r446 = Sheets("Priority Table").Range("U15") Set r447 = Sheets("Priority Table").Range("G26") Set r448 = Sheets("Priority Table").Range("N26") Set r449 = Sheets("Priority Table").Range("U26") Set r450 = Sheets("Priority Table").Range("G37") Set r451 = Sheets("Issue List").Range("O2") Set r452 = Sheets("Issue List").Range("O3") Set r453 = Sheets("Issue List").Range("O4") Set r454 = Sheets("Issue List").Range("O5") Set r455 = Sheets("Issue List").Range("O6") Set r456 = Sheets("Issue List").Range("O7") Set r457 = Sheets("Issue List").Range("O8") Set r458 = Sheets("Issue List").Range("O9") Set r459 = Sheets("Issue List").Range("O10") Set r460 = Sheets("Issue List").Range("O11") Set r461 = Sheets("Priority Table").Range("B2:G11") Set r462 = Sheets("Priority Table").Range("I2:N11") Set r463 = Sheets("Priority Table").Range("P2:U11") Set r464 = Sheets("Priority Table").Range("B13:G22") Set r465 = Sheets("Priority Table").Range("I13:N22") Set r466 = Sheets("Priority Table").Range("P13:U22") Set r467 = Sheets("Priority Table").Range("B24:G33") Set r468 = Sheets("Priority Table").Range("I24:N33") Set r469 = Sheets("Priority Table").Range("P24:U33") Set r470 = Sheets("Priority Table").Range("B35:G44")   If Not (Intersect(Target, r461) Is Nothing) Then  Application.EnableEvents = False      r221.Value = r1.Value     r231.Value = r11.Value     r251.Value = r31.Value     r261.Value = r41.Value     r271.Value = r51.Value     r281.Value = r61.Value     r291.Value = r71.Value     r301.Value = r81.Value     r311.Value = r91.Value     r321.Value = r101.Value     r331.Value = r111.Value     r451.Value = r441.Value     r361.Value = r141.Value     r371.Value = r151.Value     r381.Value = r161.Value     r391.Value = r171.Value     r401.Value = r181.Value     r411.Value = r191.Value     r421.Value = r201.Value     r431.Value = r211.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r462) Is Nothing) Then  Application.EnableEvents = False      r222.Value = r2.Value     r232.Value = r12.Value     r252.Value = r32.Value     r262.Value = r42.Value     r272.Value = r52.Value     r282.Value = r62.Value     r292.Value = r72.Value     r302.Value = r82.Value     r312.Value = r92.Value     r322.Value = r102.Value     r332.Value = r112.Value     r452.Value = r442.Value     r362.Value = r142.Value     r372.Value = r152.Value     r382.Value = r162.Value     r392.Value = r172.Value     r402.Value = r182.Value     r412.Value = r192.Value     r422.Value = r202.Value     r432.Value = r212.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r463) Is Nothing) Then  Application.EnableEvents = False      r223.Value = r3.Value     r233.Value = r13.Value     r253.Value = r33.Value     r263.Value = r43.Value     r273.Value = r53.Value     r283.Value = r63.Value     r293.Value = r73.Value     r303.Value = r83.Value     r313.Value = r93.Value     r323.Value = r103.Value     r333.Value = r113.Value     r453.Value = r443.Value     r363.Value = r143.Value     r373.Value = r153.Value     r383.Value = r163.Value     r393.Value = r173.Value     r403.Value = r183.Value     r413.Value = r193.Value     r423.Value = r203.Value     r433.Value = r213.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r464) Is Nothing) Then  Application.EnableEvents = False      r224.Value = r4.Value     r234.Value = r14.Value     r254.Value = r34.Value     r264.Value = r44.Value     r274.Value = r54.Value     r284.Value = r64.Value     r294.Value = r74.Value     r304.Value = r84.Value     r314.Value = r94.Value     r324.Value = r104.Value     r334.Value = r114.Value     r454.Value = r444.Value     r364.Value = r144.Value     r374.Value = r154.Value     r384.Value = r164.Value     r394.Value = r174.Value     r404.Value = r184.Value     r414.Value = r194.Value     r424.Value = r204.Value     r434.Value = r214.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r465) Is Nothing) Then  Application.EnableEvents = False      r225.Value = r5.Value     r235.Value = r15.Value     r255.Value = r35.Value     r265.Value = r45.Value     r275.Value = r55.Value     r285.Value = r65.Value     r295.Value = r75.Value     r305.Value = r85.Value     r315.Value = r95.Value     r325.Value = r105.Value     r335.Value = r115.Value     r455.Value = r445.Value     r365.Value = r145.Value     r375.Value = r155.Value     r385.Value = r165.Value     r395.Value = r175.Value     r405.Value = r185.Value     r415.Value = r195.Value     r425.Value = r205.Value     r435.Value = r215.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r466) Is Nothing) Then  Application.EnableEvents = False      r226.Value = r6.Value     r236.Value = r16.Value     r256.Value = r36.Value     r266.Value = r46.Value     r276.Value = r56.Value     r286.Value = r66.Value     r296.Value = r76.Value     r306.Value = r86.Value     r316.Value = r96.Value     r326.Value = r106.Value     r336.Value = r116.Value     r456.Value = r446.Value     r366.Value = r146.Value     r376.Value = r156.Value     r386.Value = r166.Value     r396.Value = r176.Value     r406.Value = r186.Value     r416.Value = r196.Value     r426.Value = r206.Value     r436.Value = r216.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r467) Is Nothing) Then  Application.EnableEvents = False      r227.Value = r7.Value     r237.Value = r17.Value     r257.Value = r37.Value     r267.Value = r47.Value     r277.Value = r57.Value     r287.Value = r67.Value     r297.Value = r77.Value     r307.Value = r87.Value     r317.Value = r97.Value     r327.Value = r107.Value     r337.Value = r117.Value     r457.Value = r447.Value     r367.Value = r147.Value     r377.Value = r157.Value     r387.Value = r167.Value     r397.Value = r177.Value     r407.Value = r187.Value     r417.Value = r197.Value     r427.Value = r207.Value     r437.Value = r217.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r468) Is Nothing) Then  Application.EnableEvents = False      r228.Value = r8.Value     r238.Value = r18.Value     r258.Value = r38.Value     r268.Value = r48.Value     r278.Value = r58.Value     r288.Value = r68.Value     r298.Value = r78.Value     r308.Value = r88.Value     r318.Value = r98.Value     r328.Value = r108.Value     r338.Value = r118.Value     r458.Value = r448.Value     r368.Value = r148.Value     r378.Value = r158.Value     r388.Value = r168.Value     r398.Value = r178.Value     r408.Value = r188.Value     r418.Value = r198.Value     r428.Value = r208.Value     r438.Value = r218.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r469) Is Nothing) Then  Application.EnableEvents = False      r229.Value = r9.Value     r239.Value = r19.Value     r259.Value = r39.Value     r269.Value = r49.Value     r279.Value = r59.Value     r289.Value = r69.Value     r299.Value = r79.Value     r309.Value = r89.Value     r319.Value = r99.Value     r329.Value = r109.Value     r339.Value = r119.Value     r459.Value = r449.Value     r369.Value = r149.Value     r379.Value = r159.Value     r389.Value = r169.Value     r399.Value = r179.Value     r409.Value = r189.Value     r419.Value = r199.Value     r429.Value = r209.Value     r439.Value = r219.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r470) Is Nothing) Then  Application.EnableEvents = False      r230.Value = r10.Value     r240.Value = r20.Value     r260.Value = r40.Value     r270.Value = r50.Value     r280.Value = r60.Value     r290.Value = r70.Value     r300.Value = r80.Value     r310.Value = r90.Value     r320.Value = r100.Value     r330.Value = r110.Value     r340.Value = r120.Value     r460.Value = r450.Value     r370.Value = r150.Value     r380.Value = r160.Value     r390.Value = r170.Value     r400.Value = r180.Value     r410.Value = r190.Value     r420.Value = r200.Value     r430.Value = r210.Value     r440.Value = r220.Value  Application.EnableEvents = True  End If  End Sub   

Mi mejor conjetura de cómo apretar el código sería mover las 470 líneas de la variable establecida como gama en un procedimiento separado que se puede acceder desde todos los diferentes procedimientos en la hoja (esas mismas 470 líneas de código son repetido en el uso del nuevo botón de emisión y el botón Priorizar los problemas). He estado buscando varias semanas para hacerlo, pero no he encontrado ninguna forma de escribir el código que me permita hacer eso.

No estoy de ninguna manera opuesta a cambiar la forma en que se nombran las variables, siempre he encontrado que las listas secuenciales son la forma más fácil de administrar grandes conjuntos de rangos.

Original en ingles

From the requests below I've attempted to rewrite my request. I agree completely that it would be easier for our company to provide us with help desk software. Unfortunately, my department has the assignment to sit between our end users and three separate help desks that deal with different problems. We are not allowed to enter in any help desk tickets until we have determined which of the help desk systems the issue belongs in, and many of the issues we handle are never taken to the help desk because we are able to resolve them through education. Our department manager has been working for years trying to get us access to a help desk with no result, so instead of waiting for the company to finally do something I've been working to build this file to give our team something we can use.

I have taken a copy of my file and manipulated it into a testing file which contains every cell that is affected by the VBA code but also meets our company's data security policies. I have included screenshots and the troublesome pieces of code below. Please let me know if there is any more information I can provide about the file.

Here is a picture of the priority list screen:

enter image description here

and here is an image of the table where the issues are stored:

enter image description here

The issue with the code that causes me the most trouble d/t bulk is the code that allows the users to freely update the issues from the first screen, and have that information directly updated on the second screen. The code functions completely but is very 'fat' so any help on slimming it down would be greatly appreciated.

The code for that process is:

Private Sub Worksheet_Change(ByVal Target As Range)  Set r1 = Sheets("Priority Table").Range("B2") Set r2 = Sheets("Priority Table").Range("I2") Set r3 = Sheets("Priority Table").Range("P2") Set r4 = Sheets("Priority Table").Range("B13") Set r5 = Sheets("Priority Table").Range("I13") Set r6 = Sheets("Priority Table").Range("P13") Set r7 = Sheets("Priority Table").Range("B24") Set r8 = Sheets("Priority Table").Range("I24") Set r9 = Sheets("Priority Table").Range("P24") Set r10 = Sheets("Priority Table").Range("B35") Set r11 = Sheets("Priority Table").Range("B4") Set r12 = Sheets("Priority Table").Range("I4") Set r13 = Sheets("Priority Table").Range("P4") Set r14 = Sheets("Priority Table").Range("B15") Set r15 = Sheets("Priority Table").Range("I15") Set r16 = Sheets("Priority Table").Range("P15") Set r17 = Sheets("Priority Table").Range("B26") Set r18 = Sheets("Priority Table").Range("I26") Set r19 = Sheets("Priority Table").Range("P26") Set r20 = Sheets("Priority Table").Range("B37") Set r21 = Sheets("Priority Table").Range("D4") Set r22 = Sheets("Priority Table").Range("K4") Set r23 = Sheets("Priority Table").Range("R4") Set r24 = Sheets("Priority Table").Range("D15") Set r25 = Sheets("Priority Table").Range("K15") Set r26 = Sheets("Priority Table").Range("R15") Set r27 = Sheets("Priority Table").Range("D26") Set r28 = Sheets("Priority Table").Range("K26") Set r29 = Sheets("Priority Table").Range("R26") Set r30 = Sheets("Priority Table").Range("D37") Set r31 = Sheets("Priority Table").Range("C8") Set r32 = Sheets("Priority Table").Range("J8") Set r33 = Sheets("Priority Table").Range("Q8") Set r34 = Sheets("Priority Table").Range("C19") Set r35 = Sheets("Priority Table").Range("J19") Set r36 = Sheets("Priority Table").Range("Q19") Set r37 = Sheets("Priority Table").Range("C30") Set r38 = Sheets("Priority Table").Range("J30") Set r39 = Sheets("Priority Table").Range("Q30") Set r40 = Sheets("Priority Table").Range("C41") Set r41 = Sheets("Priority Table").Range("C9") Set r42 = Sheets("Priority Table").Range("J9") Set r43 = Sheets("Priority Table").Range("Q9") Set r44 = Sheets("Priority Table").Range("C20") Set r45 = Sheets("Priority Table").Range("J20") Set r46 = Sheets("Priority Table").Range("Q20") Set r47 = Sheets("Priority Table").Range("C31") Set r48 = Sheets("Priority Table").Range("J31") Set r49 = Sheets("Priority Table").Range("Q31") Set r50 = Sheets("Priority Table").Range("C42") Set r51 = Sheets("Priority Table").Range("B11") Set r52 = Sheets("Priority Table").Range("I11") Set r53 = Sheets("Priority Table").Range("P11") Set r54 = Sheets("Priority Table").Range("B22") Set r55 = Sheets("Priority Table").Range("I22") Set r56 = Sheets("Priority Table").Range("P22") Set r57 = Sheets("Priority Table").Range("B33") Set r58 = Sheets("Priority Table").Range("I33") Set r59 = Sheets("Priority Table").Range("P33") Set r60 = Sheets("Priority Table").Range("B44") Set r61 = Sheets("Priority Table").Range("D6") Set r62 = Sheets("Priority Table").Range("K6") Set r63 = Sheets("Priority Table").Range("R6") Set r64 = Sheets("Priority Table").Range("D17") Set r65 = Sheets("Priority Table").Range("K17") Set r66 = Sheets("Priority Table").Range("R17") Set r67 = Sheets("Priority Table").Range("D28") Set r68 = Sheets("Priority Table").Range("K28") Set r69 = Sheets("Priority Table").Range("R28") Set r70 = Sheets("Priority Table").Range("D39") Set r71 = Sheets("Priority Table").Range("D7") Set r72 = Sheets("Priority Table").Range("K7") Set r73 = Sheets("Priority Table").Range("R7") Set r74 = Sheets("Priority Table").Range("D18") Set r75 = Sheets("Priority Table").Range("K18") Set r76 = Sheets("Priority Table").Range("R18") Set r77 = Sheets("Priority Table").Range("D29") Set r78 = Sheets("Priority Table").Range("K29") Set r79 = Sheets("Priority Table").Range("R29") Set r80 = Sheets("Priority Table").Range("D40") Set r81 = Sheets("Priority Table").Range("D8") Set r82 = Sheets("Priority Table").Range("K8") Set r83 = Sheets("Priority Table").Range("R8") Set r84 = Sheets("Priority Table").Range("D19") Set r85 = Sheets("Priority Table").Range("K19") Set r86 = Sheets("Priority Table").Range("R19") Set r87 = Sheets("Priority Table").Range("D30") Set r88 = Sheets("Priority Table").Range("K30") Set r89 = Sheets("Priority Table").Range("R30") Set r90 = Sheets("Priority Table").Range("D41") Set r91 = Sheets("Priority Table").Range("D9") Set r92 = Sheets("Priority Table").Range("K9") Set r93 = Sheets("Priority Table").Range("R9") Set r94 = Sheets("Priority Table").Range("D20") Set r95 = Sheets("Priority Table").Range("K20") Set r96 = Sheets("Priority Table").Range("R20") Set r97 = Sheets("Priority Table").Range("D31") Set r98 = Sheets("Priority Table").Range("K31") Set r99 = Sheets("Priority Table").Range("R31") Set r100 = Sheets("Priority Table").Range("D42") Set r101 = Sheets("Priority Table").Range("D10") Set r102 = Sheets("Priority Table").Range("K10") Set r103 = Sheets("Priority Table").Range("R10") Set r104 = Sheets("Priority Table").Range("D21") Set r105 = Sheets("Priority Table").Range("K21") Set r106 = Sheets("Priority Table").Range("R21") Set r107 = Sheets("Priority Table").Range("D32") Set r108 = Sheets("Priority Table").Range("K32") Set r109 = Sheets("Priority Table").Range("R32") Set r110 = Sheets("Priority Table").Range("D43") Set r111 = Sheets("Priority Table").Range("D11") Set r112 = Sheets("Priority Table").Range("K11") Set r113 = Sheets("Priority Table").Range("R11") Set r114 = Sheets("Priority Table").Range("D22") Set r115 = Sheets("Priority Table").Range("K22") Set r116 = Sheets("Priority Table").Range("R22") Set r117 = Sheets("Priority Table").Range("D33") Set r118 = Sheets("Priority Table").Range("K33") Set r119 = Sheets("Priority Table").Range("R33") Set r120 = Sheets("Priority Table").Range("D44") Set r121 = Sheets("Priority Table").Range("E4") Set r122 = Sheets("Priority Table").Range("L4") Set r123 = Sheets("Priority Table").Range("S4") Set r124 = Sheets("Priority Table").Range("E15") Set r125 = Sheets("Priority Table").Range("L15") Set r126 = Sheets("Priority Table").Range("S15") Set r127 = Sheets("Priority Table").Range("E26") Set r128 = Sheets("Priority Table").Range("L26") Set r129 = Sheets("Priority Table").Range("S26") Set r130 = Sheets("Priority Table").Range("E37") Set r131 = Sheets("Priority Table").Range("F4") Set r132 = Sheets("Priority Table").Range("M4") Set r133 = Sheets("Priority Table").Range("T4") Set r134 = Sheets("Priority Table").Range("F15") Set r135 = Sheets("Priority Table").Range("M15") Set r136 = Sheets("Priority Table").Range("S15") Set r137 = Sheets("Priority Table").Range("F26") Set r138 = Sheets("Priority Table").Range("M26") Set r139 = Sheets("Priority Table").Range("T26") Set r140 = Sheets("Priority Table").Range("F37") Set r141 = Sheets("Priority Table").Range("E6") Set r142 = Sheets("Priority Table").Range("L6") Set r143 = Sheets("Priority Table").Range("S6") Set r144 = Sheets("Priority Table").Range("E17") Set r145 = Sheets("Priority Table").Range("L17") Set r146 = Sheets("Priority Table").Range("S17") Set r147 = Sheets("Priority Table").Range("E28") Set r148 = Sheets("Priority Table").Range("L28") Set r149 = Sheets("Priority Table").Range("S28") Set r150 = Sheets("Priority Table").Range("E39") Set r151 = Sheets("Priority Table").Range("F6") Set r152 = Sheets("Priority Table").Range("M6") Set r153 = Sheets("Priority Table").Range("T6") Set r154 = Sheets("Priority Table").Range("F17") Set r155 = Sheets("Priority Table").Range("M17") Set r156 = Sheets("Priority Table").Range("T17") Set r157 = Sheets("Priority Table").Range("F28") Set r158 = Sheets("Priority Table").Range("M28") Set r159 = Sheets("Priority Table").Range("T28") Set r160 = Sheets("Priority Table").Range("F39") Set r161 = Sheets("Priority Table").Range("E8") Set r162 = Sheets("Priority Table").Range("L8") Set r163 = Sheets("Priority Table").Range("S8") Set r164 = Sheets("Priority Table").Range("E19") Set r165 = Sheets("Priority Table").Range("L19") Set r166 = Sheets("Priority Table").Range("S19") Set r167 = Sheets("Priority Table").Range("E30") Set r168 = Sheets("Priority Table").Range("L30") Set r169 = Sheets("Priority Table").Range("S30") Set r170 = Sheets("Priority Table").Range("E41") Set r171 = Sheets("Priority Table").Range("F8") Set r172 = Sheets("Priority Table").Range("M8") Set r173 = Sheets("Priority Table").Range("T8") Set r174 = Sheets("Priority Table").Range("F19") Set r175 = Sheets("Priority Table").Range("M19") Set r176 = Sheets("Priority Table").Range("T19") Set r177 = Sheets("Priority Table").Range("F30") Set r178 = Sheets("Priority Table").Range("M30") Set r179 = Sheets("Priority Table").Range("T30") Set r180 = Sheets("Priority Table").Range("F41") Set r181 = Sheets("Priority Table").Range("E10") Set r182 = Sheets("Priority Table").Range("L10") Set r183 = Sheets("Priority Table").Range("S10") Set r184 = Sheets("Priority Table").Range("E21") Set r185 = Sheets("Priority Table").Range("L21") Set r186 = Sheets("Priority Table").Range("S21") Set r187 = Sheets("Priority Table").Range("E32") Set r188 = Sheets("Priority Table").Range("L32") Set r189 = Sheets("Priority Table").Range("S32") Set r190 = Sheets("Priority Table").Range("E43") Set r191 = Sheets("Priority Table").Range("F10") Set r192 = Sheets("Priority Table").Range("M10") Set r193 = Sheets("Priority Table").Range("T10") Set r194 = Sheets("Priority Table").Range("F21") Set r195 = Sheets("Priority Table").Range("M21") Set r196 = Sheets("Priority Table").Range("T21") Set r197 = Sheets("Priority Table").Range("F32") Set r198 = Sheets("Priority Table").Range("M32") Set r199 = Sheets("Priority Table").Range("T32") Set r200 = Sheets("Priority Table").Range("F43") Set r201 = Sheets("Priority Table").Range("F11") Set r202 = Sheets("Priority Table").Range("M11") Set r203 = Sheets("Priority Table").Range("T11") Set r204 = Sheets("Priority Table").Range("F22") Set r205 = Sheets("Priority Table").Range("M22") Set r206 = Sheets("Priority Table").Range("T22") Set r207 = Sheets("Priority Table").Range("F33") Set r208 = Sheets("Priority Table").Range("M33") Set r209 = Sheets("Priority Table").Range("T33") Set r210 = Sheets("Priority Table").Range("F44") Set r211 = Sheets("Priority Table").Range("G6") Set r212 = Sheets("Priority Table").Range("N6") Set r213 = Sheets("Priority Table").Range("U6") Set r214 = Sheets("Priority Table").Range("G17") Set r215 = Sheets("Priority Table").Range("N17") Set r216 = Sheets("Priority Table").Range("U17") Set r217 = Sheets("Priority Table").Range("G28") Set r218 = Sheets("Priority Table").Range("N28") Set r219 = Sheets("Priority Table").Range("U28") Set r220 = Sheets("Priority Table").Range("G39") Set r221 = Sheets("Issue List").Range("B2") Set r222 = Sheets("Issue List").Range("B3") Set r223 = Sheets("Issue List").Range("B4") Set r224 = Sheets("Issue List").Range("B5") Set r225 = Sheets("Issue List").Range("B6") Set r226 = Sheets("Issue List").Range("B7") Set r227 = Sheets("Issue List").Range("B8") Set r228 = Sheets("Issue List").Range("B9") Set r229 = Sheets("Issue List").Range("B10") Set r230 = Sheets("Issue List").Range("B11") Set r231 = Sheets("Issue List").Range("C2") Set r232 = Sheets("Issue List").Range("C3") Set r233 = Sheets("Issue List").Range("C4") Set r234 = Sheets("Issue List").Range("C5") Set r235 = Sheets("Issue List").Range("C6") Set r236 = Sheets("Issue List").Range("C7") Set r237 = Sheets("Issue List").Range("C8") Set r238 = Sheets("Issue List").Range("C9") Set r239 = Sheets("Issue List").Range("C10") Set r240 = Sheets("Issue List").Range("C11") Set r241 = Sheets("Issue List").Range("D2") Set r242 = Sheets("Issue List").Range("D3") Set r243 = Sheets("Issue List").Range("D4") Set r244 = Sheets("Issue List").Range("D5") Set r245 = Sheets("Issue List").Range("D6") Set r246 = Sheets("Issue List").Range("D7") Set r247 = Sheets("Issue List").Range("D8") Set r248 = Sheets("Issue List").Range("D9") Set r249 = Sheets("Issue List").Range("D10") Set r250 = Sheets("Issue List").Range("D11") Set r251 = Sheets("Issue List").Range("E2") Set r252 = Sheets("Issue List").Range("E3") Set r253 = Sheets("Issue List").Range("E4") Set r254 = Sheets("Issue List").Range("E5") Set r255 = Sheets("Issue List").Range("E6") Set r256 = Sheets("Issue List").Range("E7") Set r257 = Sheets("Issue List").Range("E8") Set r258 = Sheets("Issue List").Range("E9") Set r259 = Sheets("Issue List").Range("E10") Set r260 = Sheets("Issue List").Range("E11") Set r261 = Sheets("Issue List").Range("F2") Set r262 = Sheets("Issue List").Range("F3") Set r263 = Sheets("Issue List").Range("F4") Set r264 = Sheets("Issue List").Range("F5") Set r265 = Sheets("Issue List").Range("F6") Set r266 = Sheets("Issue List").Range("F7") Set r267 = Sheets("Issue List").Range("F8") Set r268 = Sheets("Issue List").Range("F9") Set r269 = Sheets("Issue List").Range("F10") Set r270 = Sheets("Issue List").Range("F11") Set r271 = Sheets("Issue List").Range("G2") Set r272 = Sheets("Issue List").Range("G3") Set r273 = Sheets("Issue List").Range("G4") Set r274 = Sheets("Issue List").Range("G5") Set r275 = Sheets("Issue List").Range("G6") Set r276 = Sheets("Issue List").Range("G7") Set r277 = Sheets("Issue List").Range("G8") Set r278 = Sheets("Issue List").Range("G9") Set r279 = Sheets("Issue List").Range("G10") Set r280 = Sheets("Issue List").Range("G11") Set r281 = Sheets("Issue List").Range("H2") Set r282 = Sheets("Issue List").Range("H3") Set r283 = Sheets("Issue List").Range("H4") Set r284 = Sheets("Issue List").Range("H5") Set r285 = Sheets("Issue List").Range("H6") Set r286 = Sheets("Issue List").Range("H7") Set r287 = Sheets("Issue List").Range("H8") Set r288 = Sheets("Issue List").Range("H9") Set r289 = Sheets("Issue List").Range("H10") Set r290 = Sheets("Issue List").Range("H11") Set r291 = Sheets("Issue List").Range("I2") Set r292 = Sheets("Issue List").Range("I3") Set r293 = Sheets("Issue List").Range("I4") Set r294 = Sheets("Issue List").Range("I5") Set r295 = Sheets("Issue List").Range("I6") Set r296 = Sheets("Issue List").Range("I7") Set r297 = Sheets("Issue List").Range("I8") Set r298 = Sheets("Issue List").Range("I9") Set r299 = Sheets("Issue List").Range("I10") Set r300 = Sheets("Issue List").Range("I11") Set r301 = Sheets("Issue List").Range("J2") Set r302 = Sheets("Issue List").Range("J3") Set r303 = Sheets("Issue List").Range("J4") Set r304 = Sheets("Issue List").Range("J5") Set r305 = Sheets("Issue List").Range("J6") Set r306 = Sheets("Issue List").Range("J7") Set r307 = Sheets("Issue List").Range("J8") Set r308 = Sheets("Issue List").Range("J9") Set r309 = Sheets("Issue List").Range("J10") Set r310 = Sheets("Issue List").Range("J11") Set r311 = Sheets("Issue List").Range("K2") Set r312 = Sheets("Issue List").Range("K3") Set r313 = Sheets("Issue List").Range("K4") Set r314 = Sheets("Issue List").Range("K5") Set r315 = Sheets("Issue List").Range("K6") Set r316 = Sheets("Issue List").Range("K7") Set r317 = Sheets("Issue List").Range("K8") Set r318 = Sheets("Issue List").Range("K9") Set r319 = Sheets("Issue List").Range("K10") Set r320 = Sheets("Issue List").Range("K11") Set r321 = Sheets("Issue List").Range("L2") Set r322 = Sheets("Issue List").Range("L3") Set r323 = Sheets("Issue List").Range("L4") Set r324 = Sheets("Issue List").Range("L5") Set r325 = Sheets("Issue List").Range("L6") Set r326 = Sheets("Issue List").Range("L7") Set r327 = Sheets("Issue List").Range("L8") Set r328 = Sheets("Issue List").Range("L9") Set r329 = Sheets("Issue List").Range("L10") Set r330 = Sheets("Issue List").Range("L11") Set r331 = Sheets("Issue List").Range("M2") Set r332 = Sheets("Issue List").Range("M3") Set r333 = Sheets("Issue List").Range("M4") Set r334 = Sheets("Issue List").Range("M5") Set r335 = Sheets("Issue List").Range("M6") Set r336 = Sheets("Issue List").Range("M7") Set r337 = Sheets("Issue List").Range("M8") Set r338 = Sheets("Issue List").Range("M9") Set r339 = Sheets("Issue List").Range("M10") Set r340 = Sheets("Issue List").Range("M11") Set r341 = Sheets("Issue List").Range("P2") Set r342 = Sheets("Issue List").Range("P3") Set r343 = Sheets("Issue List").Range("P4") Set r344 = Sheets("Issue List").Range("P5") Set r345 = Sheets("Issue List").Range("P6") Set r346 = Sheets("Issue List").Range("P7") Set r347 = Sheets("Issue List").Range("P8") Set r348 = Sheets("Issue List").Range("P9") Set r349 = Sheets("Issue List").Range("P10") Set r350 = Sheets("Issue List").Range("P11") Set r351 = Sheets("Issue List").Range("Q2") Set r352 = Sheets("Issue List").Range("Q3") Set r353 = Sheets("Issue List").Range("Q4") Set r354 = Sheets("Issue List").Range("Q5") Set r355 = Sheets("Issue List").Range("Q6") Set r356 = Sheets("Issue List").Range("Q7") Set r357 = Sheets("Issue List").Range("Q8") Set r358 = Sheets("Issue List").Range("Q9") Set r359 = Sheets("Issue List").Range("Q10") Set r360 = Sheets("Issue List").Range("Q11") Set r361 = Sheets("Issue List").Range("R2") Set r362 = Sheets("Issue List").Range("R3") Set r363 = Sheets("Issue List").Range("R4") Set r364 = Sheets("Issue List").Range("R5") Set r365 = Sheets("Issue List").Range("R6") Set r366 = Sheets("Issue List").Range("R7") Set r367 = Sheets("Issue List").Range("R8") Set r368 = Sheets("Issue List").Range("R9") Set r369 = Sheets("Issue List").Range("R10") Set r370 = Sheets("Issue List").Range("R11") Set r371 = Sheets("Issue List").Range("S2") Set r372 = Sheets("Issue List").Range("S3") Set r373 = Sheets("Issue List").Range("S4") Set r374 = Sheets("Issue List").Range("S5") Set r375 = Sheets("Issue List").Range("S6") Set r376 = Sheets("Issue List").Range("S7") Set r377 = Sheets("Issue List").Range("S8") Set r378 = Sheets("Issue List").Range("S9") Set r379 = Sheets("Issue List").Range("S10") Set r380 = Sheets("Issue List").Range("S11") Set r381 = Sheets("Issue List").Range("T2") Set r382 = Sheets("Issue List").Range("T3") Set r383 = Sheets("Issue List").Range("T4") Set r384 = Sheets("Issue List").Range("T5") Set r385 = Sheets("Issue List").Range("T6") Set r386 = Sheets("Issue List").Range("T7") Set r387 = Sheets("Issue List").Range("T8") Set r388 = Sheets("Issue List").Range("T9") Set r389 = Sheets("Issue List").Range("T10") Set r390 = Sheets("Issue List").Range("T11") Set r391 = Sheets("Issue List").Range("U2") Set r392 = Sheets("Issue List").Range("U3") Set r393 = Sheets("Issue List").Range("U4") Set r394 = Sheets("Issue List").Range("U5") Set r395 = Sheets("Issue List").Range("U6") Set r396 = Sheets("Issue List").Range("U7") Set r397 = Sheets("Issue List").Range("U8") Set r398 = Sheets("Issue List").Range("U9") Set r399 = Sheets("Issue List").Range("U10") Set r400 = Sheets("Issue List").Range("U11") Set r401 = Sheets("Issue List").Range("V2") Set r402 = Sheets("Issue List").Range("V3") Set r403 = Sheets("Issue List").Range("V4") Set r404 = Sheets("Issue List").Range("V5") Set r405 = Sheets("Issue List").Range("V6") Set r406 = Sheets("Issue List").Range("V7") Set r407 = Sheets("Issue List").Range("V8") Set r408 = Sheets("Issue List").Range("V9") Set r409 = Sheets("Issue List").Range("V10") Set r410 = Sheets("Issue List").Range("V11") Set r411 = Sheets("Issue List").Range("W2") Set r412 = Sheets("Issue List").Range("W3") Set r413 = Sheets("Issue List").Range("W4") Set r414 = Sheets("Issue List").Range("W5") Set r415 = Sheets("Issue List").Range("W6") Set r416 = Sheets("Issue List").Range("W7") Set r417 = Sheets("Issue List").Range("W8") Set r418 = Sheets("Issue List").Range("W9") Set r419 = Sheets("Issue List").Range("W10") Set r420 = Sheets("Issue List").Range("W11") Set r421 = Sheets("Issue List").Range("X2") Set r422 = Sheets("Issue List").Range("X3") Set r423 = Sheets("Issue List").Range("X4") Set r424 = Sheets("Issue List").Range("X5") Set r425 = Sheets("Issue List").Range("X6") Set r426 = Sheets("Issue List").Range("X7") Set r427 = Sheets("Issue List").Range("X8") Set r428 = Sheets("Issue List").Range("X9") Set r429 = Sheets("Issue List").Range("X10") Set r430 = Sheets("Issue List").Range("X11") Set r431 = Sheets("Issue List").Range("Y2") Set r432 = Sheets("Issue List").Range("Y3") Set r433 = Sheets("Issue List").Range("Y4") Set r434 = Sheets("Issue List").Range("Y5") Set r435 = Sheets("Issue List").Range("Y6") Set r436 = Sheets("Issue List").Range("Y7") Set r437 = Sheets("Issue List").Range("Y8") Set r438 = Sheets("Issue List").Range("Y9") Set r439 = Sheets("Issue List").Range("Y10") Set r440 = Sheets("Issue List").Range("Y11") Set r441 = Sheets("Priority Table").Range("G4") Set r442 = Sheets("Priority Table").Range("N4") Set r443 = Sheets("Priority Table").Range("U4") Set r444 = Sheets("Priority Table").Range("G15") Set r445 = Sheets("Priority Table").Range("N15") Set r446 = Sheets("Priority Table").Range("U15") Set r447 = Sheets("Priority Table").Range("G26") Set r448 = Sheets("Priority Table").Range("N26") Set r449 = Sheets("Priority Table").Range("U26") Set r450 = Sheets("Priority Table").Range("G37") Set r451 = Sheets("Issue List").Range("O2") Set r452 = Sheets("Issue List").Range("O3") Set r453 = Sheets("Issue List").Range("O4") Set r454 = Sheets("Issue List").Range("O5") Set r455 = Sheets("Issue List").Range("O6") Set r456 = Sheets("Issue List").Range("O7") Set r457 = Sheets("Issue List").Range("O8") Set r458 = Sheets("Issue List").Range("O9") Set r459 = Sheets("Issue List").Range("O10") Set r460 = Sheets("Issue List").Range("O11") Set r461 = Sheets("Priority Table").Range("B2:G11") Set r462 = Sheets("Priority Table").Range("I2:N11") Set r463 = Sheets("Priority Table").Range("P2:U11") Set r464 = Sheets("Priority Table").Range("B13:G22") Set r465 = Sheets("Priority Table").Range("I13:N22") Set r466 = Sheets("Priority Table").Range("P13:U22") Set r467 = Sheets("Priority Table").Range("B24:G33") Set r468 = Sheets("Priority Table").Range("I24:N33") Set r469 = Sheets("Priority Table").Range("P24:U33") Set r470 = Sheets("Priority Table").Range("B35:G44")   If Not (Intersect(Target, r461) Is Nothing) Then  Application.EnableEvents = False      r221.Value = r1.Value     r231.Value = r11.Value     r251.Value = r31.Value     r261.Value = r41.Value     r271.Value = r51.Value     r281.Value = r61.Value     r291.Value = r71.Value     r301.Value = r81.Value     r311.Value = r91.Value     r321.Value = r101.Value     r331.Value = r111.Value     r451.Value = r441.Value     r361.Value = r141.Value     r371.Value = r151.Value     r381.Value = r161.Value     r391.Value = r171.Value     r401.Value = r181.Value     r411.Value = r191.Value     r421.Value = r201.Value     r431.Value = r211.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r462) Is Nothing) Then  Application.EnableEvents = False      r222.Value = r2.Value     r232.Value = r12.Value     r252.Value = r32.Value     r262.Value = r42.Value     r272.Value = r52.Value     r282.Value = r62.Value     r292.Value = r72.Value     r302.Value = r82.Value     r312.Value = r92.Value     r322.Value = r102.Value     r332.Value = r112.Value     r452.Value = r442.Value     r362.Value = r142.Value     r372.Value = r152.Value     r382.Value = r162.Value     r392.Value = r172.Value     r402.Value = r182.Value     r412.Value = r192.Value     r422.Value = r202.Value     r432.Value = r212.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r463) Is Nothing) Then  Application.EnableEvents = False      r223.Value = r3.Value     r233.Value = r13.Value     r253.Value = r33.Value     r263.Value = r43.Value     r273.Value = r53.Value     r283.Value = r63.Value     r293.Value = r73.Value     r303.Value = r83.Value     r313.Value = r93.Value     r323.Value = r103.Value     r333.Value = r113.Value     r453.Value = r443.Value     r363.Value = r143.Value     r373.Value = r153.Value     r383.Value = r163.Value     r393.Value = r173.Value     r403.Value = r183.Value     r413.Value = r193.Value     r423.Value = r203.Value     r433.Value = r213.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r464) Is Nothing) Then  Application.EnableEvents = False      r224.Value = r4.Value     r234.Value = r14.Value     r254.Value = r34.Value     r264.Value = r44.Value     r274.Value = r54.Value     r284.Value = r64.Value     r294.Value = r74.Value     r304.Value = r84.Value     r314.Value = r94.Value     r324.Value = r104.Value     r334.Value = r114.Value     r454.Value = r444.Value     r364.Value = r144.Value     r374.Value = r154.Value     r384.Value = r164.Value     r394.Value = r174.Value     r404.Value = r184.Value     r414.Value = r194.Value     r424.Value = r204.Value     r434.Value = r214.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r465) Is Nothing) Then  Application.EnableEvents = False      r225.Value = r5.Value     r235.Value = r15.Value     r255.Value = r35.Value     r265.Value = r45.Value     r275.Value = r55.Value     r285.Value = r65.Value     r295.Value = r75.Value     r305.Value = r85.Value     r315.Value = r95.Value     r325.Value = r105.Value     r335.Value = r115.Value     r455.Value = r445.Value     r365.Value = r145.Value     r375.Value = r155.Value     r385.Value = r165.Value     r395.Value = r175.Value     r405.Value = r185.Value     r415.Value = r195.Value     r425.Value = r205.Value     r435.Value = r215.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r466) Is Nothing) Then  Application.EnableEvents = False      r226.Value = r6.Value     r236.Value = r16.Value     r256.Value = r36.Value     r266.Value = r46.Value     r276.Value = r56.Value     r286.Value = r66.Value     r296.Value = r76.Value     r306.Value = r86.Value     r316.Value = r96.Value     r326.Value = r106.Value     r336.Value = r116.Value     r456.Value = r446.Value     r366.Value = r146.Value     r376.Value = r156.Value     r386.Value = r166.Value     r396.Value = r176.Value     r406.Value = r186.Value     r416.Value = r196.Value     r426.Value = r206.Value     r436.Value = r216.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r467) Is Nothing) Then  Application.EnableEvents = False      r227.Value = r7.Value     r237.Value = r17.Value     r257.Value = r37.Value     r267.Value = r47.Value     r277.Value = r57.Value     r287.Value = r67.Value     r297.Value = r77.Value     r307.Value = r87.Value     r317.Value = r97.Value     r327.Value = r107.Value     r337.Value = r117.Value     r457.Value = r447.Value     r367.Value = r147.Value     r377.Value = r157.Value     r387.Value = r167.Value     r397.Value = r177.Value     r407.Value = r187.Value     r417.Value = r197.Value     r427.Value = r207.Value     r437.Value = r217.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r468) Is Nothing) Then  Application.EnableEvents = False      r228.Value = r8.Value     r238.Value = r18.Value     r258.Value = r38.Value     r268.Value = r48.Value     r278.Value = r58.Value     r288.Value = r68.Value     r298.Value = r78.Value     r308.Value = r88.Value     r318.Value = r98.Value     r328.Value = r108.Value     r338.Value = r118.Value     r458.Value = r448.Value     r368.Value = r148.Value     r378.Value = r158.Value     r388.Value = r168.Value     r398.Value = r178.Value     r408.Value = r188.Value     r418.Value = r198.Value     r428.Value = r208.Value     r438.Value = r218.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r469) Is Nothing) Then  Application.EnableEvents = False      r229.Value = r9.Value     r239.Value = r19.Value     r259.Value = r39.Value     r269.Value = r49.Value     r279.Value = r59.Value     r289.Value = r69.Value     r299.Value = r79.Value     r309.Value = r89.Value     r319.Value = r99.Value     r329.Value = r109.Value     r339.Value = r119.Value     r459.Value = r449.Value     r369.Value = r149.Value     r379.Value = r159.Value     r389.Value = r169.Value     r399.Value = r179.Value     r409.Value = r189.Value     r419.Value = r199.Value     r429.Value = r209.Value     r439.Value = r219.Value  Application.EnableEvents = True  End If  If Not (Intersect(Target, r470) Is Nothing) Then  Application.EnableEvents = False      r230.Value = r10.Value     r240.Value = r20.Value     r260.Value = r40.Value     r270.Value = r50.Value     r280.Value = r60.Value     r290.Value = r70.Value     r300.Value = r80.Value     r310.Value = r90.Value     r320.Value = r100.Value     r330.Value = r110.Value     r340.Value = r120.Value     r460.Value = r450.Value     r370.Value = r150.Value     r380.Value = r160.Value     r390.Value = r170.Value     r400.Value = r180.Value     r410.Value = r190.Value     r420.Value = r200.Value     r430.Value = r210.Value     r440.Value = r220.Value  Application.EnableEvents = True  End If  End Sub 

My best guess of how to tighten up the code would be to move the 470 lines of set variable as range into a separate procedure that could be accessed from all of the different procedures in the sheet (those same 470 lines of code are repeated in the use of both the New Issue button and the Prioritize Issues button). I have been looking for several weeks for a way to do that but have failed to find any way of writing the code that allows me to do that.

I am not in any way opposed to changing the way the variables are named, I've just always found sequential lists to be the easiest way to manage large sets of ranges.

     
     
     

Lista de respuestas

4
 
vote

Tienes 12 tablas idénticamente formateadas; Esperaría un código legible / mantenible para que lea lo que tiene algún tipo de concepto para una de estas tablas. Un módulo de clase hubiera sido perfecto para esto. En su lugar, estamos viendo una lista secuencial completamente críptica de las variables de objeto no menos que 470 ], 60 de las cuales se asignan, pero nunca se usan en cualquier lugar del alcance del procedimiento.

El procedimiento en cuestión es un manejador de eventos que se invoca cada vez que se produce cualquier cosa en esa hoja de trabajo: su contenido debe ser mínimo y actor, para evitar la lentitud.

El procedimiento del controlador es deferenciar la "tabla de prioridad" y las hojas de trabajo "Lista de emisión" en innumerables veces ... que es inútil por encima. Podría haberlo leferenciado una vez , con un bloque memset(new_mem + to->cap, 0, to->cap); 6

      memset(new_mem + to->cap, 0, to->cap); 7  

Pero eso no está abordando al elefante en la habitación en absoluto: bajo ninguna circunstancia, es una buena idea tener 470 variables en ninguna parte. No en ese procedimiento, tampoco en otro. El enfoque es simplemente incorrecto.

Sin mencionar, ninguna de estas variables se declara en realidad: agregar memset(new_mem + to->cap, 0, to->cap); 8 en la parte superior de su módulo, y luego obtener su código para compilar, será un poco de dolor en el cuello.


Dije anteriormente:

tiene 12 tablas idénticamente formateadas; Esperaría un código legible / mantenible que lea como si tuviera algún tipo de concepto para una de estas tablas.

La clave aquí es abstracción - Esa es la diferencia entre una grabadora macro-grabadora , y un programa bien escrito .

que comenzaría por nombrar cosas . Tome una de estas 12 mesas, y lo describe :

  • Hay un memset(new_mem + to->cap, 0, to->cap); 9 , o size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 0 en una celda fusionada en la parte superior.
  • Hay un código para el size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 1 .
  • Hay un size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 2 Fecha.
  • Hay un valor booleano para indicar si size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 3
  • Hay otro valor booleano para indicar si 99887766555443334
  • Hay un código 99887766555443335 . Haría las dos últimas tablas (encabezados rojos), se adhieren al formato de las otras tablas, y mueva el size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 6 que se dirige hacia abajo para que coincida con la posición del size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 37 encabezado en cada otra mesa.
  • Hay una lista de hasta 6 size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 88 códigos.
  • Hay un size_t old_cap = to->cap; to->cap *= LOAD_FACTOR; memset(new_mem + old_cap, 0, to->cap - old_cap); 9
  • Hay un sb_free_copy()0
  • Hay un sb_free_copy()1
  • Hay un código sb_free_copy()2
  • Hay un código
  • Hay un código sb_free_copy()4
  • Hay un campo
  • Hay un código sb_free_copy()6655443346 que posiblemente se refiere a cada código de seguimiento y el código 998877766555443347 .

Haría un módulo de clase que expone todo lo anterior como propiedades de lectura. Luego agregaría algunas propiedades metáticas para que el objeto de la tabla pueda saber cómo "dibujar" en sí mismo:

  • sb_free_copy()8 sb_free_copy()9 , para describir la posición de la celda izquierda en el encabezado de la tabla.

Luego expondría un método / procedimiento / método / procedimiento que toma un parámetro de hoja de trabajo y descarga sus valores en esa hoja: el procedimiento sabría cómo compensar cada valor del sb_free_copy()1 < / Código> Y sb_free_copy()2 Posición.

Tendría algún código para crear y configurar las instancias de 12 de esa clase en el alcance global, para que el controlador pueda acceder a ellos. Y debido a que las variables de nombramiento con un sufijo numérico es un olor a código que básicamente dice "Necesita una estructura de datos", en realidad declaré 1 variable: una matriz que contiene los 12 objetos:

  sb_free_copy()3  

El controlador sb_free_copy()4 podría llamar a ese procedimiento 99887766655443355 en un bucle, para que tenga sus 12 tablas en la matriz global tan pronto como el libro de trabajo esté abierto.


Sin embargo,

Hay otro elefante en la habitación: Sin embargo, toda la macro, se asigna una celda dada el valor de otra celda. 410 veces.

Parece que toda la macro podría reemplazarse con fórmulas de hoja de trabajo haciendo sb_free_copy()6 , donde 99887776655443357 es la que se supone que se refiere a la celda.

Por ejemplo, sb_free_copy()8 es sb_free_copy()9 , por lo que el formul En char *sb_free_copy(StringBuilder sb) { char *ret = sb->mem; sb->mem = NULL; sb_free(sb); return ret; } 0 podría simplemente ser char *sb_free_copy(StringBuilder sb) { char *ret = sb->mem; sb->mem = NULL; sb_free(sb); return ret; } 1 , y así sucesivamente, para todo; La hoja de trabajo se mantendría actualizada sin involucrar ningún código VBA.

 

You have 12 identically-formatted tables; I would expect readable/maintainable code to read like it has some kind of concept for one of these tables. A class module would have been perfect for this. Instead we're looking at a completely cryptic sequential list of no less than 470 [undeclared] object variables, 60 of which are assigned but never used anywhere in the procedure's scope.

The procedure in question is an event handler that gets invoked everytime anything changes on that worksheet: its content should be minimal and performant, to avoid sluggishness.

The handler procedure is dereferencing the "Priority Table" and "Issue List" worksheets countless times... which is useless overhead. You could have dereferenced it once, with a With block:

With ThisWorkbook.Worksheets("Priority Table")     Set r1 = .Range("B2")     '... End With  With ThisWorkbook.Worksheets("Issue List")     Set r221 = .Range("B2")     '... End With 

But that's not addressing the elephant in the room at all: under no circumstance is it ever a good idea to have 470 variables anywhere. Not in that procedure, not in another either. The approach is simply wrong.

Not to mention, none of these variables are actually declared: adding Option Explicit at the top of your module, and then getting your code to compile, is going to be quite a pain in the neck.


I said above:

You have 12 identically-formatted tables; I would expect readable/maintainable code to read like it has some kind of concept for one of these tables.

The key here is abstraction - that's the difference between a macro-recorder script, and a well-written program.

I would start by naming things. Take one of these 12 tables, and describe it:

  • There's a Heading, or Title in a merged cell at the top.
  • There's a code for the IssueSummary.
  • There's a DateReported date.
  • There's a Boolean value to indicate whether it HasNextFollowUp
  • There's another Boolean value to indicate whether it IsDue
  • There's an AdjustFollowUp code. I'd make the last two tables (red headings) adhere to the format of the other tables, and move the Notes heading down to match the position of the Notes heading in every other table.
  • There's a list of up to 6 ReportBackTo codes.
  • There's a PriorityCode
  • There's a TicketFieldCode
  • There's a TicketNumber
  • There's a FirstFollowUp code
  • There's a SecondFollowUp code
  • There's a ThirdFollowUp code
  • There's a Notes field
  • There's an OnTime code that's not quite clear, that possibly refers to each follow-up codes, and the Resolved code.

I'd make a class module that exposes all the above as read-write properties. Then I'd add some meta-properties so that the table object can know how to "draw" itself:

  • HeadingRow and HeadingColumn, to describe the position of the left-most cell in the table's header.

Then I'd expose some Update method/procedure, that takes a worksheet parameter and dumps its values into that sheet - the procedure would know how to offset each value from the HeadingRow and HeadingColumn position.

I'd have some code to create and configure the 12 instances of that class in global scope, so that the handler can access them. And because naming variables with a numeric suffix is a code smell that basically says "you need a data structure", I'd actually be declaring 1 variable - an array that contains the 12 objects:

Option Explicit Public PriorityTables(1 To 12) As PriorityTable  Public Sub InitializeTable(ByVal index As Long, ByVal headerCell As Range)     Set PriorityTables(index) = New PriorityTable     With PriorityTables(index)         .HeadingRow = headerCell.Row         .HeadingColumn = headerCell.Column         .Update Sheet1     End With End Sub 

The Workbook_Open handler could conceivably call that InitializeTable procedure in a loop, so you have your 12 tables in the global array as soon as the workbook is open.


There's another elephant in the room though: all the macro does, is assign a given cell the value of another cell. 410 times.

It looks like the entire macro could be replaced with worksheet formulas doing =TheOtherSheet!ZZ42, where ZZ42 is whatever cell is supposed to be referred to.

For example, r440.Value is r220.Value, so the formula in 'Issue List'!Y11 could simply be ='Priority Table'!G39, and so on, for everything; the worksheet would keep itself up-to-date without involving any VBA code.

 
 

Relacionados problema

3  Comparando células de dos hojas de trabajo  ( Comparing cells of two worksheets ) 
Con el siguiente código, trato de comparar el contenido de dos hojas de trabajo. La primera columna contiene una clave que es única y las siguientes columnas ...

3  Coloque el cálculo del costo del campamento de verano utilizando las hojas de Google  ( Summer camp cost calculation using google sheets ) 
Estoy haciendo una hoja que suma el costo total para un campamento de verano después del registro. Cada padre puede registrarse hasta cinco niños. Ya he hecho...

5  Excel a JSON Parser con http descargar  ( Excel to json parser with http download ) 
He estado trabajando en un proyecto ( enlace ) a Descargue una hoja de cálculo de Ransomware y propiedades conocidas y conviértase en JSON para que pueda cons...

2  Importando datos en Excel  ( Importing data into excel ) 
¿Existe una forma más fácil de importar datos en una matriz de Excel u otra estructura de datos? He intentado investigar colecciones, pero he encontrado la D...

7  Recuperando datos de archivos en la carpeta  ( Retrieving data from files in the folder ) 
Estoy a usar el código VBA para simplemente recuperar ciertos datos de todos los archivos de Excel en cierta carpeta y pegarlo en la hoja de cálculo de trabaj...

2  Búsqueda de pruebas de abstracción y unidad en mesa Excel  ( Abstracting and unit testing lookups in excel table ) 
Fondo Tengo una solución de VBA que utilizo para ingerir informes de texto de inversión y reformatearlos para su análisis en Excel. Funciona, pero las macro...

6  Fijación de archivos de reclamación médica a través del archivo de texto Lea / escribe  ( Fixing medical claim files through text file read write ) 
Con Gracias a @ Mat'Smug y @comintern por su aliento, aquí hay un programa que escribí para ayudar a mi equipo a reparar archivos de reclamación médica. El ...

6  Crear tabla que enumera la visibilidad de la hoja de trabajo  ( Create table that lists worksheet visibility ) 
Siguiendo mi pregunta anterior crear Una tabla que enumera las macros en un libro de trabajo o hoja de trabajo Aquí está mi Sub para determinar la visibilid...

5  Copiando datos de libros cerrados  ( Copying data from closed workbooks ) 
Soy un pasante en una empresa industrial en Brasil y sucede que estoy usando mucho en Excel. Acabo de empezar a jugar con VBA hace un par de días, ¡y me divie...

6  Juego de serpientes - Dentro de la hoja de trabajo - Celdas como píxeles  ( Snake game within worksheet cells as pixels ) 
Desde mi intento bastante mediocre de hacer un juego de invasores del espacio, me topé con un caché de juegos de Visual Basic para solicitar juegos escritos p...




© 2022 respuesta.top Reservados todos los derechos. Centro de preguntas y respuestas reservados todos los derechos