Napisao sam funkciju koja racuna bilo koju od subtotal funkcija (9=SUM) za odredjeni skup celija a prema rednom broju stranice koja se stampa
Code:
Public Function RunningSubtotal(numPage As Long, numFunction As Byte, rngCells As String) As Double
Dim lFirst As Long
Dim lLast As Long
Dim lPage As Long
Dim lRowStart As Long
lRowStart = 1 ' prvi red iz PrintArea
lFirst = Range(rngCells).Row
lLast = lFirst + Range(rngCells).Rows.Count - 1
lPage = 0
For Each x In ActiveSheet.HPageBreaks
lPage = lPage + 1
If lPage = numPage Then
RunningSubtotal = Application.WorksheetFunction.Subtotal(numFunction, _
Intersect(Range(rngCells), Range(Range(Cells(lRowStart, Range(rngCells).Column).Address & ":" & Cells(x.Location.Row - 1, Range(rngCells).Column).Address).Address)))
End If
lRowStart = x.Location.Row
Next
' last page
If numPage = lPage + 1 Then
RunningSubtotal = Application.WorksheetFunction.Subtotal(numFunction, _
Intersect(Range(rngCells), Range(Range(Cells(lRowStart, Range(rngCells).Column).Address & ":" & Cells(lLast, Range(rngCells).Column).Address).Address)))
End If
End Function
Poziva se sa:
Code:
RunningSubtotal(rb_stranice,rb_funkcije,skup_celija)
Npr sledeci kod ce izracunati koji je medjuzbir tvojih celija (koje su od H2:H150) a koje se stampaju na 2. strani
Code:
Public Sub izracunaj()
MsgBox RunningSubtotal(2, 9, "popis!H2:H150")
End Sub
Nije nikakav problem postaviti funkciju u Before_Print dogadjas Workbook-a tako da je ubacimo u Header/Footer
ali mi ipak ostaje problem kako da na svakoj stranici promenim redni broj strane za koji da racuna medjuzbir.
Tol'ko za sada.
Pozdrav