Private Sub Worksheet_SelectionChange(ByVal Target As Range)'формула массиваIf Target.Address = "$B$3" Then Range("B14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),1),B3)" Range("B14:B22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("C14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31), LARGE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(1))),12,31),1),C3)" Range("C14:C22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("D14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),1),C3)-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),1),B3+1))+1" Range("D14:D22").Select Selection.NumberFormat = "#,##0" Selection.HorizontalAlignment = xlCenter Range("E14").Formula2 = "=IF(DAY(DATE(ROW(INDIRECT(YEAR(B3)&"":""& YEAR(C3))),2,29))=29,366,365)" Range("E14:E22").Select Selection.NumberFormat = "#,##0" Selection.HorizontalAlignment = xlCenter Range("F14").Formula2 = "=(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),1),C3)-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),1),B3+1))+1)/IF(DAY(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),2,29))=29,366,365)" Range("F14:F22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("G14").Formula2 = "=$E$3*$F$3%*(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),12,31),1),C3)-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),1,1),1),B3+1))+1)/IF(DAY" & _ "(DATE(ROW(INDIRECT(YEAR(B3)&"":""&YEAR(C3))),2,29))=29,366,365)" Range("G14:G22").Select Selection.NumberFormat = "#,##0.00" Selection.HorizontalAlignment = xlRightEnd IfEnd Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'формула массива 'отслеживаем только диапазон "C3:C13" If Not Intersect(Target, Range("C3:C13")) Is Nothing Then Dim lr& lr = Target.Row Application.ScreenUpdating = False Range("B14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),1),B" & lr & ")" Range("B14:B22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("C14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31), LARGE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(1))),12,31),1),C" & lr & ")" Range("C14:C22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("D14").Formula2 = "=--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),1),C" & lr & ")-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),1),B" & lr & "+1))+1" Range("D14:D22").Select Selection.NumberFormat = "#,##0" Selection.HorizontalAlignment = xlCenter Range("E14").Formula2 = "=IF(DAY(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""& YEAR(C" & lr & "))),2,29))=29,366,365)" Range("E14:E22").Select Selection.NumberFormat = "#,##0" Selection.HorizontalAlignment = xlCenter Range("F14").Formula2 = "=(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),1),C" & lr & ")-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),1),B" & lr & "+1))+1)/IF(DAY(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),2,29))=29,366,365)" Range("F14:F22").Select Selection.NumberFormat = "m/d/yyyy" Selection.HorizontalAlignment = xlCenter Range("G14").Formula2 = "=$E$3*$F$3%*(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),LARGE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),12,31),1),C" & lr & ")-(--SUBSTITUTE(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),SMALL(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),1,1),1),B" & lr & "+1))+1)/IF(DAY" & _ "(DATE(ROW(INDIRECT(YEAR(B" & lr & ")&"":""&YEAR(C" & lr & "))),2,29))=29,366,365)" Range("G14:G22").Select Selection.NumberFormat = "#,##0.00" Selection.HorizontalAlignment = xlRight Application.ScreenUpdating = True End IfEnd Sub