PDA

View Full Version : Solved: Formula - Runtime 1004 Error



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

rory
09-06-2007, 04:23 AM
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
09-06-2007, 04:43 AM
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
ActiveCell.FormulaR1C1 = "=SE0001!R[37]C[7]"
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
09-06-2007, 05:25 AM
Try something like this:

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


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

NewDaddy
09-06-2007, 05:31 AM
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
09-06-2007, 05:45 AM
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
09-06-2007, 07:24 AM
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
FRML = "=" & rngFound.Offset(, 14).Address
to
FRML = "=" & Sht.Name & "!" & rngFound.Offset(, 14).Address

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

Cheers
J