Изменение кода написанного макрорекордером - VBA
Формулировка задачи:
Записал макрорекордером автоматический ввод формул в определённые диапазоны ячеек.Как можно подредактировать или упростить код получившегося макроса,чтобы ввод формул был одномоментным при выполнении этого макроса,а не происходил путем последовательного перебора тех ячеек в которые вводятся формулы? Если увеличить диапазон ячеек то перебором время исполнения макроса затягивается.
Sub Ввод_1 () ' Range("CH22").Select ActiveCell.Formula = "=TD236" Range("CH21").Select ActiveCell.Formula = "=TC236" Range("CH20").Select ActiveCell.Formula = "=TB236" Range("CH19").Select ActiveCell.Formula = "=TA236" Range("CH18").Select ActiveCell.Formula = "=SZ236" Range("CH17").Select ActiveCell.Formula = "=SY236" Range("CH16").Select ActiveCell.Formula = "=SX236" Range("CH15").Select ActiveCell.Formula = "=SW236" Range("CH14").Select ActiveCell.Formula = "=SV236" Range("CH13").Select ActiveCell.Formula = "=SU236" Range("CH12").Select ActiveCell.Formula = "=ST236" Range("CH11").Select ActiveCell.Formula = "=SS236" Range("CH10").Select ActiveCell.Formula = "=SR236" Range("CH9").Select ActiveCell.Formula = "=SQ236" Range("CH8").Select ActiveCell.Formula = "=SP236" Range("CH7").Select ActiveCell.Formula = "=SO236" Range("CH6").Select ActiveCell.Formula = "=SN236" Range("CH5").Select ActiveCell.Formula = "=SM236" Range("CH4").Select ActiveCell.Formula = "=SL236" Range("CG22").Select ActiveCell.Formula = "=SK236" Range("CG21").Select ActiveCell.Formula = "=SJ236" Range("CG20").Select ActiveCell.Formula = "=SI236" Range("CG19").Select ActiveCell.Formula = "=SH236" Range("CG18").Select ActiveCell.Formula = "=SG236" Range("CG17").Select ActiveCell.Formula = "=SF236" Range("CG16").Select ActiveCell.Formula = "=SE236" Range("CG15").Select ActiveCell.Formula = "=SD236" Range("CG14").Select ActiveCell.Formula = "=SC236" Range("CG13").Select ActiveCell.Formula = "=SB236" Range("CG12").Select ActiveCell.Formula = "=SA236" Range("CG11").Select ActiveCell.Formula = "=RZ236" Range("CG10").Select ActiveCell.Formula = "=RY236" Range("CG9").Select ActiveCell.Formula = "=RX236" Range("CG8").Select ActiveCell.Formula = "=RW236" Range("CG7").Select ActiveCell.Formula = "=RV236" Range("CG6").Select ActiveCell.Formula = "=RU236" Range("CG5").Select ActiveCell.Formula = "=RT236" Range("CG4").Select ActiveCell.Formula = "=RS236" Range("CF22").Select ActiveCell.Formula = "=RR236" Range("CF21").Select ActiveCell.Formula = "=RQ236" Range("CF20").Select ActiveCell.Formula = "=RP236" Range("CF19").Select ActiveCell.Formula = "=RO236" Range("CF18").Select ActiveCell.Formula = "=RN236" Range("CF17").Select ActiveCell.Formula = "=RM236" Range("CF16").Select ActiveCell.Formula = "=RL236" Range("CF15").Select ActiveCell.Formula = "=RK236" Range("CF14").Select ActiveCell.Formula = "=RJ236" Range("CF13").Select ActiveCell.Formula = "=RI236" Range("CF12").Select ActiveCell.Formula = "=RH236" Range("CF11").Select ActiveCell.Formula = "=RG236" Range("CF10").Select ActiveCell.Formula = "=RF236" Range("CF9").Select ActiveCell.Formula = "=RE236" Range("CF8").Select ActiveCell.Formula = "=RD236" Range("CF7").Select ActiveCell.Formula = "=RC236" Range("CF6").Select ActiveCell.Formula = "=RB236" Range("CF5").Select ActiveCell.Formula = "=RA236" Range("CF4").Select ActiveCell.Formula = "=QZ236" Range("CE22").Select ActiveCell.Formula = "=QY236" Range("CE21").Select ActiveCell.Formula = "=QX236" Range("CE20").Select ActiveCell.Formula = "=QW236" Range("CE19").Select ActiveCell.Formula = "=QV236" Range("CE18").Select ActiveCell.Formula = "=QU236" Range("CE17").Select ActiveCell.Formula = "=QT236" Range("CE16").Select ActiveCell.Formula = "=QS236" Range("CE15").Select ActiveCell.Formula = "=QR236" Range("CE14").Select ActiveCell.Formula = "=QQ236" Range("CE13").Select ActiveCell.Formula = "=QP236" Range("CE12").Select ActiveCell.Formula = "=QO236" Range("CE11").Select ActiveCell.Formula = "=QN236" Range("CE10").Select ActiveCell.Formula = "=QM236" Range("CE9").Select ActiveCell.Formula = "=QL236" Range("CE8").Select ActiveCell.Formula = "=QK236" Range("CE7").Select ActiveCell.Formula = "=QJ236" Range("CE6").Select ActiveCell.Formula = "=QI236" Range("CE5").Select ActiveCell.Formula = "=QH236" Range("CE4").Select ActiveCell.Formula = "=QG236" Range("BX22").Select ActiveCell.Formula = "=QE236" Range("BX21").Select ActiveCell.Formula = "=QD236" Range("BX20").Select ActiveCell.Formula = "=QC236" Range("BX19").Select ActiveCell.Formula = "=QB236" Range("BX18").Select ActiveCell.Formula = "=QA236" Range("BX17").Select ActiveCell.Formula = "=PZ236" Range("BX16").Select ActiveCell.Formula = "=PY236" Range("BX15").Select ActiveCell.Formula = "=PX236" Range("BX14").Select ActiveCell.Formula = "=PW236" Range("BX13").Select ActiveCell.Formula = "=PV236" Range("BX12").Select ActiveCell.Formula = "=PU236" Range("BX11").Select ActiveCell.Formula = "=PT236" Range("BX10").Select ActiveCell.Formula = "=PS236" Range("BX9").Select ActiveCell.Formula = "=PR236" Range("BX8").Select ActiveCell.Formula = "=PQ236" Range("BX7").Select ActiveCell.Formula = "=PP236" Range("BX6").Select ActiveCell.Formula = "=PO236" Range("BX5").Select ActiveCell.Formula = "=PN236" Range("BX4").Select ActiveCell.Formula = "=PM236" Range("BW22").Select ActiveCell.Formula = "=PL236" Range("BW21").Select ActiveCell.Formula = "=PK236" Range("BW20").Select ActiveCell.Formula = "=PJ236" Range("BW19").Select ActiveCell.Formula = "=PI236" Range("BW18").Select ActiveCell.Formula = "=PH236" Range("BW17").Select ActiveCell.Formula = "=PG236" Range("BW16").Select ActiveCell.Formula = "=PF236" Range("BW15").Select ActiveCell.Formula = "=PE236" Range("BW14").Select ActiveCell.Formula = "=PD236" Range("BW13").Select ActiveCell.Formula = "=PC236" Range("BW12").Select ActiveCell.Formula = "=PB236" Range("BW11").Select ActiveCell.Formula = "=PA236" Range("BW10").Select ActiveCell.Formula = "=OZ236" Range("BW9").Select ActiveCell.Formula = "=OY236" Range("BW8").Select ActiveCell.Formula = "=OX236" Range("BW7").Select ActiveCell.Formula = "=OW236" Range("BW6").Select ActiveCell.Formula = "=OV236" Range("BW5").Select ActiveCell.Formula = "=OU236" Range("BW4").Select ActiveCell.Formula = "=OT236" Range("BV22").Select ActiveCell.Formula = "=OS236" Range("BV21").Select ActiveCell.Formula = "=OR236" Range("BV20").Select ActiveCell.Formula = "=OQ236" Range("BV19").Select ActiveCell.Formula = "=OP236" Range("BV18").Select ActiveCell.Formula = "=OO236" Range("BV17").Select ActiveCell.Formula = "=ON236" Range("BV16").Select ActiveCell.Formula = "=OM236" Range("BV15").Select ActiveCell.Formula = "=OL236" Range("BV14").Select ActiveCell.Formula = "=OK236" Range("BV13").Select ActiveCell.Formula = "=OJ236" Range("BV12").Select ActiveCell.Formula = "=OI236" Range("BV11").Select ActiveCell.Formula = "=OH236" Range("BV10").Select ActiveCell.Formula = "=OG236" Range("BV9").Select ActiveCell.Formula = "=OF236" Range("BV8").Select ActiveCell.Formula = "=OE236" Range("BV7").Select ActiveCell.Formula = "=OD236" Range("BV6").Select ActiveCell.Formula = "=OC236" Range("BV5").Select ActiveCell.Formula = "=OB236" Range("BV4").Select ActiveCell.Formula = "=OA236" Range("BU22").Select ActiveCell.Formula = "=NZ236" Range("BU21").Select ActiveCell.Formula = "=NY236" Range("BU20").Select ActiveCell.Formula = "=NX236" Range("BU19").Select ActiveCell.Formula = "=NW236" Range("BU18").Select ActiveCell.Formula = "=NV236" Range("BU17").Select ActiveCell.Formula = "=NU236" Range("BU16").Select ActiveCell.Formula = "=NT236" Range("BU15").Select ActiveCell.Formula = "=NS236" Range("BU14").Select ActiveCell.Formula = "=NR236" Range("BU13").Select ActiveCell.Formula = "=NQ236" Range("BU12").Select ActiveCell.Formula = "=NP236" Range("BU11").Select ActiveCell.Formula = "=NO236" Range("BU10").Select ActiveCell.Formula = "=NN236" Range("BU9").Select ActiveCell.Formula = "=NM236" Range("BU8").Select ActiveCell.Formula = "=NL236" Range("BU7").Select ActiveCell.Formula = "=NK236" Range("BU6").Select ActiveCell.Formula = "=NJ236" Range("BU5").Select ActiveCell.Formula = "=NI236" Range("BU4").Select ActiveCell.Formula = "=NH236" Range("BN22").Select ActiveCell.Formula = "=NE236" Range("BN21").Select ActiveCell.Formula = "=ND236" Range("BN20").Select ActiveCell.Formula = "=NC236" Range("BN19").Select ActiveCell.Formula = "=NB236" Range("BN18").Select ActiveCell.Formula = "=NA236" Range("BN17").Select ActiveCell.Formula = "=MZ236" Range("BN16").Select ActiveCell.Formula = "=MY236" Range("BN15").Select ActiveCell.Formula = "=MX236" Range("BN14").Select ActiveCell.Formula = "=MW236" Range("BN13").Select ActiveCell.Formula = "=MV236" Range("BN12").Select ActiveCell.Formula = "=MU236" Range("BN11").Select ActiveCell.Formula = "=MT236" Range("BN10").Select ActiveCell.Formula = "=MS236" Range("BN9").Select ActiveCell.Formula = "=MR236" Range("BN8").Select ActiveCell.Formula = "=MQ236" Range("BN7").Select ActiveCell.Formula = "=MP236" Range("BN6").Select ActiveCell.Formula = "=MO236" Range("BN5").Select ActiveCell.Formula = "=MN236" Range("BN4").Select ActiveCell.Formula = "=MM236" Range("BM22").Select ActiveCell.Formula = "=ML236" Range("BM21").Select ActiveCell.Formula = "=MK236" Range("BM20").Select ActiveCell.Formula = "=MJ236" Range("BM19").Select ActiveCell.Formula = "=MI236" Range("BM18").Select ActiveCell.Formula = "=MH236" Range("BM17").Select ActiveCell.Formula = "=MG236" Range("BM16").Select ActiveCell.Formula = "=MF236" Range("BM15").Select ActiveCell.Formula = "=ME236" Range("BM14").Select ActiveCell.Formula = "=MD236" Range("BM13").Select ActiveCell.Formula = "=MC236" Range("BM12").Select ActiveCell.Formula = "=MB236" Range("BM11").Select ActiveCell.Formula = "=MA236" Range("BM10").Select ActiveCell.Formula = "=LZ236" Range("BM9").Select ActiveCell.Formula = "=LY236" Range("BM8").Select ActiveCell.Formula = "=LX236" Range("BM7").Select ActiveCell.Formula = "=LW236" Range("BM6").Select ActiveCell.Formula = "=LV236" Range("BM5").Select ActiveCell.Formula = "=LU236" Range("BM4").Select ActiveCell.Formula = "=LT236" Range("BL22").Select ActiveCell.Formula = "=LS236" Range("BL21").Select ActiveCell.Formula = "=LR236" Range("BL20").Select ActiveCell.Formula = "=LQ236" Range("BL19").Select ActiveCell.Formula = "=LP236" Range("BL18").Select ActiveCell.Formula = "=LO236" Range("BL17").Select ActiveCell.Formula = "=LN236" Range("BL16").Select ActiveCell.Formula = "=LM236" Range("BL15").Select ActiveCell.Formula = "=LL236" Range("BL14").Select ActiveCell.Formula = "=LK236" Range("BL13").Select ActiveCell.Formula = "=LJ236" Range("BL12").Select ActiveCell.Formula = "=LI236" Range("BL11").Select ActiveCell.Formula = "=LH236" Range("BL10").Select ActiveCell.Formula = "=LG236" Range("BL9").Select ActiveCell.Formula = "=LF236" Range("BL8").Select ActiveCell.Formula = "=LE236" Range("BL7").Select ActiveCell.Formula = "=LD236" Range("BL6").Select ActiveCell.Formula = "=LC236" Range("BL5").Select ActiveCell.Formula = "=LB236" Range("BL4").Select ActiveCell.Formula = "=LA236" Range("BK22").Select ActiveCell.Formula = "=KZ236" Range("BK21").Select ActiveCell.Formula = "=KY236" Range("BK20").Select ActiveCell.Formula = "=KX236" Range("BK19").Select ActiveCell.Formula = "=KW236" Range("BK18").Select ActiveCell.Formula = "=KV236" Range("BK17").Select ActiveCell.Formula = "=KU236" Range("BK16").Select ActiveCell.Formula = "=KT236" Range("BK15").Select ActiveCell.Formula = "=KS236" Range("BK14").Select ActiveCell.Formula = "=KR236" Range("BK13").Select ActiveCell.Formula = "=KQ236" Range("BK12").Select ActiveCell.Formula = "=KP236" Range("BK11").Select ActiveCell.Formula = "=KO236" Range("BK10").Select ActiveCell.Formula = "=KN236" Range("BK9").Select ActiveCell.Formula = "=KM236" Range("BK8").Select ActiveCell.Formula = "=KL236" Range("BK7").Select ActiveCell.Formula = "=KK236" Range("BK6").Select ActiveCell.Formula = "=KJ236" Range("BK5").Select ActiveCell.Formula = "=KI236" Range("BK4").Select End Sub
Решение задачи: «Изменение кода написанного макрорекордером»
textual
Листинг программы
Sub Заполнить_BK4_CH22() Dim i&, j&, jj&, k& k = 293 For jj = 0 To 2 For j = 1 To 4 For i = 1 To 19 k = k + 1 Cells(i + 3, 62 + j + jj * 10).FormulaR1C1 = "=R236C" & k Next i Next j k = k +2- jj Next jj End Sub
ИИ поможет Вам:
- решить любую задачу по программированию
- объяснить код
- расставить комментарии в коде
- и т.д