Solved: Formula - Runtime 1004 Error

[NewDaddy]

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)
[VBA].Offset(, 1).Value = BProj[/VBA]

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 -
[VBA].Offset(, 1).Formula = FRML[/VBA]
I am getting a Runtime error 1004: Application-defined or object-defined error 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
[VBA]'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[/VBA]

[rory]

First, you should have a check to ensure that the text is found in the sheet.
Second, why do you need a sum formula when you are only referring to one cell?
Third, why do you strip off the last character of the found cell's address? That is probably your issue.

[NewDaddy]

First, you should have a check to ensure that the text is found in the sheet.
Second, why do you need a sum formula when you are only referring to one cell?
Third, why do you strip off the last character of the found cell's address? That is probably your issue.

Hi Rory
1st - I think I understand, I will try.
2nd - because I haven't managed to do the following
[vba]ActiveCell.FormulaR1C1 = "=SE0001!R[37]C[7]"[/vba]
but using variables for the sheet reference???
This is what should really be used, I suppose, instead of a sum formula, but like I said before I have never been able to workout how to do these using variables to refer to either the sheet, row or column?
If you or someone could explain I would really appriciate it.
3rd Because it replaces the last char (") with a close braket.
so it changes "=SUM($O$10" with "=SUM($O$10)"

Cheers
Jay

[rory]

Try something like this:

[VBA] Dim rngFound As Range
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

Set rngFound = sht.Cells.Find("Total Cost Centre")
If Not rngFound Is Nothing Then
FRML = "=" & rngFound.Offset(, 14).Address
.Offset(, 1).Formula = FRML
Set rngFound = Nothing
End If

End If
End With
End If
Next sht
[/VBA]

Re 3, you are not stripping off a final quote mark, you are actually removing the last character of the cell address.

[NewDaddy]

Thanks Rory.
I will try it out.

What is the significance of Set anyway? The explaination under help is gobbledegoop. I don't know what it is but I cannot seem to understand what it's explaining (trying) to me....

Again thanks a million.

[rory]

You use Set when assigning an object to a variable - e.g. you assign a worksheet, range, or workbook object to a variable. If you are just assigning a value or property to a variable, you don't need it. (You used to use Let for that instead of Set, but that's unnecessary now unless you are declaring class properties)
So you would use:
Set objSheet = Range("B1")
but just:
varValue = Range("B1").Value

Does that help?

[NewDaddy]

Hi Rory

Thanks for explaining that. I understand it better.
The best way to learn is to experiment!

Just one change, I thought I would post for everyone’s benefit. I changed
[vba]FRML = "=" & rngFound.Offset(, 14).Address[/vba]
to
[vba]FRML = "=" & Sht.Name & "!" & rngFound.Offset(, 14).Address[/vba]

Again thanks for your help on this and the lesson it is greatly appreciated.

Cheers
J