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.

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.

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.