ExcelBanter - Run-time error '1004':

Here is a method that relies on a custom user-defined function.

Put this formula in a cell on your summary page:
=SumSalaries( 5/15/2005, "Sum1", "Sum3", "A1", "B1" )
Sum1 and Sum3 are the first and last individual sheets, respectively. All
other individual sheets must be between these two sheets.
A1 is the address that contains the date to check on each individ. sheet and
B1 is the address to sum (tuition).

Put this code in a module in your visual basic editor:

Public Function SumSalaries(strCheckDate As String, strSheetStart As String,
strSheetEnd As String, strAddressCheck As String, strAddressSum As String) As
Double
Application.Volatile

Dim wkb As Workbook
Dim wks As Worksheet
Dim lngStart As Long
Dim lngEnd As Long
Dim i As Long
Dim dblTotal As Long

Set wkb = ThisWorkbook

If (Not (ExcelSheetExists(wkb, strSheetStart))) Then
Call MsgBox("Sheet (" & strSheetStart & ") does not exist!")
Exit Function
End If

If (Not (ExcelSheetExists(wkb, strSheetEnd))) Then
Call MsgBox("Sheet (" & strSheetEnd & ") does not exist!")
Exit Function
End If

lngStart = wkb.Worksheets(strSheetStart).Index
lngEnd = wkb.Worksheets(strSheetEnd).Index

strCheckDate = Format(strCheckDate, "mm/dd/yyyy")
dblTotal = 0

For i = lngStart To lngEnd
Set wks = wkb.Worksheets(i)
If (Format(wks.Range(strAddressCheck).Value, "mm/dd/yyyy") =
strCheckDate) Then
dblTotal = dblTotal + wks.Range(strAddressSum).Value
End If
Next i

SumSalaries = dblTotal

Set wkb = Nothing

End Function

Public Function ExcelSheetExists(wkb As Workbook, strSheet As String) As
Boolean
Dim wks As Worksheet

On Error GoTo ErrHandler

Set wks = wkb.Worksheets(strSheet)
ExcelSheetExists = True

Exit Function
ErrHandler:
ExcelSheetExists = False
End Function

"Type of Sheet displayed" wrote:

I'm getting a really hard error when I code to put a formula into a worksheet
that works as a normal formula in the worksheet. I'm building 500 tables
(worksheet B) that contain detailed breakdown data for each single entry in
another worksheet (Worksheet A). So for each Worksheet A entry there is a
detailed breakdown table. I'm trying to write code to create the remaining
499 other tables. This is a one time use of code (vice creating each one
manually that cannot be done by recording a macro and running it) to save
time. I'm stuck on this:

Run-time error '1004':

Application-defined or object-defined error

The code above this works fine but this generates the error:

'Now Fix the Formulas for Effort Hours
Worksheets("Data-Tables").Range("F" & (STARTCELL + (OFFSET * _
LoopCounter) + 3)).Formula = "=IF(Utilize_Nominal_Estimate, _
IF(ISERROR((((E626/$E$617)*$C$565)*Sizing!$Z$6)), 'No Entry', _
(((E626/$E$617)*$C$565)*Sizing!$Z$6)), _
IF(ISERROR((((E626/$E$617)*$C$565)*Sizing!$O$6)), 'No Entry', _
(((E626/$E$617)*$C$565)*Sizing!$O$6)))"

I had to break this line up a little more to make it readable here. If I
don't use the ISERROR code the thing runs fine. However, with it the code
crashes. I've started with the formula copied of the worksheet that already
works start using loopcounters, offsets, and such to fix the hard references
to point the the right cells.

Any ideas?

Thanks,

Bryan