Setting Range.Formula with custom function

[beeawwb]

Hi all,

I recently set up some functions based on Chip Pearson's tutorial for referencing worksheets from Formulas. (http://www.cpearson.com/excel/sheetref.htm)

The problem I'm now having is that I can't use VBA to set these functions in place; it returns a syntax error.

For example, I want I37 on most sheets to have the same formula. So I have the following:

For Each Worksheet In Worksheets

Select Case Worksheet.Index

Case 1, 2, 3
'Do Nothing

Case Else
'The below returns a syntax error (highlighted in red)
Worksheets(Worksheet.Index).Range("I37").Formula = "=RefOnPrevSheet("I39"))"

End Select

Next Worksheet

When I type the formula directly in the cell, and debug.print it, it shows exactly what I've put in quotes. How can I put this formula into my cells without returning a syntax error?

Cheers,

Bob

[Trebor76]

Hi Bob,

The main issue was that you hadn't closed off the Select Case statement.

Post back re how the following goes (note that I've included the relevant udf by cpearson for an entire working solution):

Sub test()

    For Each Worksheet In Worksheets
        Select Case Worksheet.Index
            Case 1, 2, 3
                'Do Nothing
            Case Else
                Worksheets(Worksheet.Index).Range("I37").Formula = "=RefOnPrevSheet(""I39"")"
        End Select
    
    Next Worksheet

End Sub
Function RefOnPrevSheet(Addr As String) As Variant
    Application.Volatile True
    With Application.Caller.Parent
    If .Index = 1 Then
        RefOnPrevSheet = _
        .Parent.Worksheets(.Parent.Worksheets.Count).Range(Addr).Value
    Else
        RefOnPrevSheet = .Previous.Range(Addr).Value
    End If
    End With
End Function

HTH

Robert

[beeawwb]

Hi Robert,

Thanks for your help. Actually the Select Case wouldn't have shown as an error yet since I hadn't compiled/run through it yet. As it was, I did actually notice the Select Case issue earlier and fixed it up.

What *was* the problem was I hadn't enclosed the I39 in double double quotes.

Now I just need to work out why it's throwing an Application Error (1004) when running...

Cheers!

Bob

Edit: Actually, what seems to be happening is it's setting the formula a #VALUE! instead of the text enclosed in brackets. I.e. Running the function instead of placing the text. Will work through it after lunch...

[beeawwb]

I worked out what was going on. The formula was working, it just wasn't showing in Step Through mode as each formula had to update 1 by 1. When running completely it worked fine.

Thread Solved.