Answer the question
In order to leave comments, you need to log in
Why is it not possible to adjust the counted number of lines before summing up?
Good afternoon, help me find the error, I can’t understand why only 14 rows are counted once, and then strictly 18 each time until the end of the data in the table.
The script substitutes an empty line at the end of each page where it calculates the total for 13 and 15 columns of the table.
Sub Print_Title()
Dim a%, b&, d&, e, n&
e = ActiveSheet.PageSetup.PrintTitleRows 'Select '.Count '= "$1:$7"
e = Range(e).Rows.Count
ActiveSheet.Copy Before:=Sheets(2)
ActiveSheet.UsedRange.Value = ActiveSheet.UsedRange.Value
ActiveSheet.DrawingObjects(1).Text = "Print"
ActiveSheet.DrawingObjects(1).OnAction = "KuklP"
a = ActiveSheet.HPageBreaks(1).Location.Row
b = Cells(Rows.Count, 1).End(xlUp).Row
d = a: n = 14 ' первый раз отсчитвваем 14 строк и подбиваем итого (дальше почему то подбивается только после 18 строки)
While d < b
Rows(d - 2).Insert ' пустая строка
Rows(d - 2).Insert ' пустая строка в которую происходит запись
Cells(d - 1, 12).Value = "ИТОГО" ' пишем в 12 колонку слово Итого
Cells(d - 1, 13).Formula = "=SUM(" & Range(Cells(n, 13), Cells(d - 3, 13)).Address(0, 0) & ")" ' подсчет суммы в 13 колонке
Cells(d - 1, 15).Formula = "=SUM(" & Range(Cells(n, 15), Cells(d - 3, 15)).Address(0, 0) & ")" ' подсчет суммы в 15 колонке
Cells(d - 1, 13).AutoFill Range(Cells(d - 1, 13), Cells(d - 1, 16)), 0 ' записываем подсчитаное в стоке
n = d
d = a + d - e + 4
b = Cells(Rows.Count, 1).End(xlUp).Row + 2
Wend
Rows(d - 2).Insert ' пустая строка
Rows(d - 2).Insert ' пустая строка в которую происходит запись
Cells(d - 1, 12).Value = "ИТОГО" ' пишем в 12 колонку слово Итого
Cells(d - 1, 13).Formula = "=SUM(" & Range(Cells(n, 13), Cells(d - 3, 13)).Address(0, 0) & ")" ' подсчет суммы в 13 колонке
Cells(d - 1, 15).Formula = "=SUM(" & Range(Cells(n, 15), Cells(d - 3, 15)).Address(0, 0) & ")" ' подсчет суммы в 15 колонке
Cells(d - 1, 13).AutoFill Range(Cells(d - 1, 13), Cells(d - 1, 16)), 0 ' записываем подсчитаное в стоке
'Cells(b, 12).Value = "ИТОГО"
'Cells(b, 13).Formula = "=SUM(" & Range(Cells(n, 13), Cells(b - 2, 13)).Address(0, 0) & ")"
'Cells(b, 15).Formula = "=SUM(" & Range(Cells(n, 15), Cells(b - 2, 15)).Address(0, 0) & ")"
'Cells(b, 13).AutoFill Range(Cells(b, 13), Cells(b, 16)), 0
End Sub
Answer the question
In order to leave comments, you need to log in
Didn't find what you were looking for?
Ask your questionAsk a Question
731 491 924 answers to any question