Private Sub все_правильные_формулы_итожки()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
Application.DisplayStatusBar = False
Application.DisplayAlerts = False
ActiveSheet.Unprotect Password:="1111"
If Left(ActiveSheet.name, 2) = "БЛ" Then
d = 26
Else
d = 15
End If
lRow1 = Cells(Rows.Count, 12).End(xlUp).Row
For i = d To lRow1
If Cells(i, 25).Value > 0 And Cells(i + 1, 12).Value > 0 Then
a = i
c = i
End If
For j = 1 To 300
If Cells(j + c + 1, 12).Value > 0 Then
Else
b = j + c
Exit For
End If
Next
If a = i Then
Cells(a, 17).FormulaR1C1 = "=SUM(R[1]C:R" & b & "C)"
For u = 32 To 55 Step 2
If Cells(a, u).HasFormula Then
Cells(a, u).FormulaR1C1 = "=IF(AND(RC17=0,R[2]C1=""ч"",R1C1>0),R[1]C1,IF(RC17>0,R[1]C1,0))"
Cells(a, u + 1).FormulaR1C1 = "=IF(AND(RC17=0,R[2]C1=""д"",R1C1>0),R[1]C1,SUM(R[1]C:R" & b & "C))"
End If
Next
End If
Next
lRow = Cells(Rows.Count, 12).End(xlUp).Row
For i = d To lRow
If Cells(i, 25).Value > 0 And Cells(i + 1, 12).Value > 0 Then
Cells(i, 5).FormulaR1C1 = "=IF(AND(RC[12]=0,R[2]C[-4]=""ч"",R1C1>0),SUM(RC[27]+RC[29]+RC[31]+RC[33]+RC[35]+RC[37]+RC[39]+RC[41]+RC[43]+RC[45]+RC[47]+RC[49]),IF(RC[12]>0,SUM(RC[27]+RC[29]+RC[31]+RC[33]+RC[35]+RC[37]+RC[39]+RC[41]+RC[43]+RC[45]+RC[47]+RC[49]),0))"
Cells(i, 6).FormulaR1C1 = "=R[0]C[1]+R[0]C[2]+R[0]C[5]"
Cells(i, 7).FormulaR1C1 = "=IF(AND(RC[10]=0,R[2]C[-6]=""д"",R1C1>0,RC[17]=""" & Range("BG5").Value & """),SUM(RC[26]+RC[28]+RC[30]+RC[32]+RC[34]+RC[36]+RC[38]+RC[40]+RC[42]+RC[44]+RC[46]+RC[48]),IF(RC[17]=""" & Range("BG5").Value & """,RC[-2]*R5C61,0))"
Cells(i, 8).FormulaR1C1 = "=R[0]C[1]+R[0]C[2]"
Cells(i, 9).FormulaR1C1 = "=IF(AND(RC[8]=0,R[2]C[-8]=""д"",R1C1>0,RC[15]=""" & Range("BG6").Value & """),SUM(RC[24]+RC[26]+RC[28]+RC[30]+RC[32]+RC[34]+RC[36]+RC[38]+RC[40]+RC[42]+RC[44]+RC[46]),IF(RC[15]=""" & Range("BG6").Value & """,RC[-4]*R6C61,0))"
Cells(i, 10).FormulaR1C1 = "=IF(AND(RC[7]=0,R[2]C[-9]=""д"",R1C1>0,RC[14]=""" & Range("BG7").Value & """),SUM(RC[23]+RC[25]+RC[27]+RC[29]+RC[31]+RC[33]+RC[35]+RC[37]+RC[39]+RC[41]+RC[43]+RC[45]),IF(RC[14]=""" & Range("BG7").Value & """,RC[-5]*R7C61,0))"
If Cells(i + 1, 23).Value <> "Изоляция" Then
Cells(i, 11).FormulaR1C1 = "=IF(AND(RC[6]=0,R[2]C[-10]=""д"",R1C1>0,RC[13]=""" & Range("BG8").Value & """),SUM(RC[22]+RC[24]+RC[26]+RC[28]+RC[30]+RC[32]+RC[34]+RC[36]+RC[38]+RC[40]+RC[42]+RC[44]),IF(RC[13]=""" & Range("BG8").Value & """,RC[-6]*R8C61,0))"
Else
Cells(i, 11).FormulaR1C1 = "=IF(AND(RC[6]=0,R[2]C[-10]=""д"",R1C1>0,OR(RC[13]=""" & Range("BG8").Value & """,RC[13]=""" & Range("BG9").Value & """)),SUM(RC[22]+RC[24]+RC[26]+RC[28]+RC[30]+RC[32]+RC[34]+RC[36]+RC[38]+RC[40]+RC[42]+RC[44]),IF(RC[13]=""" & Range("BG8").Value & """,RC[-6]*R8C61,IF(RC[13]=""" & Range("BG9").Value & """,RC[-6]*R9C61,0)))"
End If
Cells(i, 18).FormulaR1C1 = "=R[0]C[-1]+R[0]C[-12]"
End If
Next
ActiveSheet.Protect Password:="1111", UserInterfaceOnly:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True, _
AllowFormattingRows:=True, AllowFiltering:=True
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
Application.DisplayStatusBar = True
Application.DisplayAlerts = True
Application.Calculation = xlAutomatic
End Sub
|