Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Proglem
I have a userform with the following line
Totals.Caption = PageCount() & " Pages Indexed This calls up a function with the following Function PageCount( Dim Sht As Workshee Dim Rng As Rang PageCount = For Each Sht In ActiveWorkbook.Worksheet On Error Resume Nex If Sht.Visible = True The Set Rng = Sht.Range("Page_Totals" Total = Application.Sum(Rng End I PageCount = PageCount + Tota Next Sh End Functio The Range 'Rng' consists of 9 individual cells Every worksheet has the same layout and same range name. I have a macro that copies a master worksheet and renames it. The number of worksheets can and will vary My Problem This works except that it calculates one of the worksheets twice. So instead of getting 1200 + 800 = 2000, I am getting 1200 + 800 + 800 = 2800 I know I am missing something. Programming VBA is a lot like trying to find Waldo. The answer is right there in front of you but damned if I cant see it...LO Any and all help will be enthusiastically blessed. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Proglem
Works fine for me.
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Troy" wrote in message ... I have a userform with the following line: Totals.Caption = PageCount() & " Pages Indexed" This calls up a function with the following: Function PageCount() Dim Sht As Worksheet Dim Rng As Range PageCount = 0 For Each Sht In ActiveWorkbook.Worksheets On Error Resume Next If Sht.Visible = True Then Set Rng = Sht.Range("Page_Totals") Total = Application.Sum(Rng) End If PageCount = PageCount + Total Next Sht End Function The Range 'Rng' consists of 9 individual cells. Every worksheet has the same layout and same range name. I have a macro that copies a master worksheet and renames it. The number of worksheets can and will vary. My Problem: This works except that it calculates one of the worksheets twice. So instead of getting 1200 + 800 = 2000, I am getting 1200 + 800 + 800 = 2800. I know I am missing something. Programming VBA is a lot like trying to find Waldo. The answer is right there in front of you but damned if I cant see it...LOL Any and all help will be enthusiastically blessed. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Summing Proglem
Found my own answer. It was right under my nose. Dang that Waldo..
The answer was that I needed the 'PageCount = PageCount + Total' to be inside the If Sht.Visible = True section (or whatever it's called). Once I moved it, it worked as expected ----- Troy wrote: ---- I have a userform with the following line Totals.Caption = PageCount() & " Pages Indexed This calls up a function with the following Function PageCount( Dim Sht As Workshee Dim Rng As Rang PageCount = For Each Sht In ActiveWorkbook.Worksheet On Error Resume Nex If Sht.Visible = True The Set Rng = Sht.Range("Page_Totals" Total = Application.Sum(Rng End I PageCount = PageCount + Tota Next Sh End Functio The Range 'Rng' consists of 9 individual cells Every worksheet has the same layout and same range name. I have a macro that copies a master worksheet and renames it. The number of worksheets can and will vary My Problem This works except that it calculates one of the worksheets twice. So instead of getting 1200 + 800 = 2000, I am getting 1200 + 800 + 800 = 2800 I know I am missing something. Programming VBA is a lot like trying to find Waldo. The answer is right there in front of you but damned if I cant see it...LO Any and all help will be enthusiastically blessed. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing | Excel Discussion (Misc queries) | |||
Summing | Excel Worksheet Functions | |||
Summing not summing | Excel Worksheet Functions | |||
PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
Result Proglem | New Users to Excel |