NewDaddy
09-06-2007, 04:12 AM
Hi All
I am customising a table of contents sub (found on the internet) to include references to the sheets within the workbook.
I have managed to do this doing the following (full section of code shown at end of post)
.Offset(, 1).Value = BProj
However as the amounts being referred to can change I need to refer to them by a ?SUM? formula, and this is where I have a problem. Using the following -
.Offset(, 1).Formula = FRML
I am getting a Runtime error 1004: Application-defined or object-defined error (http://www.xtremevbtalk.com/showthread.php?t=222658) on this line. I have search to understand this error but cannot understand the problem?
Can anyone help fix the problem and explain (in stupid terms) what is this error and/or what to look out for?
Thanks a million, Jay
Full Section of code
'Create the table
For Each Sht In ActiveWorkbook.Worksheets
If Not Sht Is ws Then
'Check all the worksheets except WS
'Now, add the Sht reference to WS
With ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1)
.Value = Sht.Name
ws.Hyperlinks.Add Anchor:=.Item(1), Address:="", _
SubAddress:="'" & Sht.Name & "'!A1"
'Add a bullet in column A
.Offset(, -1).Value = Chr$(149)
'Add in totals of projections
If Sht.Name Like "[A-Z][A-Z]####" Then
I want to replace this;
BProj = Sht.Cells.Find("Total Cost Centre").Offset(, 14).Value
.Offset(, 1).Value = BProj '<=============THIS WORKS FINE
BProj = ""
With this:
FRML = "=SUM("
FRML = FRML & Sht.Cells.Find("Total Cost Centre").Offset(, 14).Address
FRML = Left(FRML, Len(FRML) - 1) & ")"
.Offset(, 1).Formula = FRML '<=============OFFENDING LINE
End If
End With
End If
Next Sht
I am customising a table of contents sub (found on the internet) to include references to the sheets within the workbook.
I have managed to do this doing the following (full section of code shown at end of post)
.Offset(, 1).Value = BProj
However as the amounts being referred to can change I need to refer to them by a ?SUM? formula, and this is where I have a problem. Using the following -
.Offset(, 1).Formula = FRML
I am getting a Runtime error 1004: Application-defined or object-defined error (http://www.xtremevbtalk.com/showthread.php?t=222658) on this line. I have search to understand this error but cannot understand the problem?
Can anyone help fix the problem and explain (in stupid terms) what is this error and/or what to look out for?
Thanks a million, Jay
Full Section of code
'Create the table
For Each Sht In ActiveWorkbook.Worksheets
If Not Sht Is ws Then
'Check all the worksheets except WS
'Now, add the Sht reference to WS
With ws.Cells(ws.Rows.Count, 2).End(xlUp).Offset(1)
.Value = Sht.Name
ws.Hyperlinks.Add Anchor:=.Item(1), Address:="", _
SubAddress:="'" & Sht.Name & "'!A1"
'Add a bullet in column A
.Offset(, -1).Value = Chr$(149)
'Add in totals of projections
If Sht.Name Like "[A-Z][A-Z]####" Then
I want to replace this;
BProj = Sht.Cells.Find("Total Cost Centre").Offset(, 14).Value
.Offset(, 1).Value = BProj '<=============THIS WORKS FINE
BProj = ""
With this:
FRML = "=SUM("
FRML = FRML & Sht.Cells.Find("Total Cost Centre").Offset(, 14).Address
FRML = Left(FRML, Len(FRML) - 1) & ")"
.Offset(, 1).Formula = FRML '<=============OFFENDING LINE
End If
End With
End If
Next Sht