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:

Please Login or Register  to view this content.

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):

Please Login or Register  to view this content.

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.