• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

how to insert a formula in footer?

Nima

New Member
hi to all,

i have a big data sheet and when i want to print it it break in several page. i want show total of some columns in each sheet when i am printing.

i Know i could insert a content of a cell into footer like below:
.....
With ActiveSheet
.PageSetup.CenterFooter = .Range("A1").Text
....

but exactly i want to no use of any cell to insert content in footer.
is there any solution to insert a formula like sum() in footer to act individually?
 
Hi ,

Can you try something like this ?

Activesheet.Pagesetup.Centerfooter = Evaluate("=SUM(G1:G4)")

The footer will then display the sum of the contents of cells G1 through G4.

If your formula is going to be more complex than this , post it here , and we can see how the Evaluate function will have to be rewritten ; for more information , you can go through this link :

http://fastexcel.wordpress.com/2011...to-make-excels-evaluate-method-twice-as-fast/

Narayan
 
dear narayank991,

thanks for your msg but your cod just shows sum of G1:G4 in footer of each sheet but i want to show sum of cells in G in each page. for exm first page G1:G50 next page G51:G100 next page G101:G150, ... .
 
Hi ,

OK. So how will the code know which page is being printed ? If you know the page number , then it is a simple matter to use the OFFSET function such as :

=SUM(OFFSET($G$1,(pagenumber-1)*50,,50))

What this does is use a rowoffset which increments by 50 each time , since pagenumber increases by 1 each time , and this gives an offset of 50 , 100 , 150 ,... Each time , 50 rows are summed up , since the 4th parameter is 50.

Narayan
 
Hi ,

We cannot continue troubleshooting like this !

Can you either post the complete print macro , or upload your file ?

Narayan
 
i don't have any print macro that is why i ask to help.

I want to have sum of DRG in each print sheet.
 
Hi ,

If you want a print macro , which will change the footer in each page , it will take some time ; hope you can wait.

Narayan
 
Dear Narayank,

thanks for your efforts but unfortunately your code shows just sum of first page in footer of all page!
also i don't want to print all page i should see dox first in print preview and decide to print some of them although with changing .printout to .printpreview it should be done.

waiting for your comment.
 
further to above pls see below cod

PHP:
Public Sub SetFooter()
Dim ws As Worksheet
Dim x As Double
Application.ScreenUpdating = False
ThisWorkbook.Worksheets("Sheet1").Activate
    With ActiveSheet
        .PageSetup.PrintArea = "A6:H1087"
            Page_Nomber = Range(.PageSetup.PrintArea).Rows.Count \ 50 + IIf(Range(.PageSetup.PrintArea).Rows.Count Mod 50 > 0, 1, 0)
 
                For i = 1 To Page_Nomber
                    .PageSetup.CenterFooter = .Range("H50").Text
                Next i
 
    End With
 
End Sub

i used below formulas in cell H50:

H50 = =IF(MOD(ROW(),45)=5,SUM(OFFSET(E50,-44,-1,45,1)),"")

45 +5 = 50 rows in each page

the problem is how to insert sum of each page which shown in cell H50,H100,H150,... in footer of page 1,page2,page3,...
 

Attachments

  • sample sheet.xlsm
    93.8 KB · Views: 41
Hi ,

I am sorry but when I tried out the code , I tried out with the PDF creator PrimoPDF , and the totals on each page were the page totals ; they were all not the same.

I hope someone else helps you out.

Narayan
 
Hi ,

I replaced the .Printout statement by the .PrintPreview statement , and I saw that the Print Preview screen shows the same total on all the pages ; but this is not correct , since the Print Preview statement is within the For ... Next loop ; hence , when you see any page on the Print Preview screen , either print out the active page to the printer , or press the Esc key or the click the Close Preview button to not print that page.

If you do print that page to the printer , the next page which is displayed on the screen will have the correct total for the next page in the footer , though again , the same total will be displayed on all pages.

The point is that you print one page at a time.

Narayan
 
Dear Narayan,

thanks for your efforts. every thing is in order now.

but just one more question
how about last page?
when i add new rows last page maybe is not a complete page, i mean each complete print page for example have 50 rows if my last Page has 23 rows total running didn't show sum for this 23 rows.
what is your comment to sort out this issue?
 
Hi ,

As long as the rows below your table are blank , there should not be any problem , since the formula will always add up the values available in 50 rows , and blanks will be the same as zeros.

If you have any data below your table , then there is a problem. Can you confirm this ?

Narayan
 
Back
Top