+ Reply to Thread
Results 1 to 4 of 4

Setting Range.Formula with custom function

  1. #1
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105

    Setting Range.Formula with custom function

    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
    Last edited by beeawwb; 05-18-2009 at 11:56 PM.

  2. #2
    Forum Expert
    Join Date
    12-10-2006
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    3,565

    Re: Setting Range.Formula with custom function

    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
    ____________________________________________
    Please ensure you mark your thread as Solved once it is. Click here to see how
    If this post helps, please don't forget to say thanks by clicking the star icon in the bottom left-hand corner of my post

  3. #3
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105

    Re: Setting Range.Formula with custom function

    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...
    Last edited by beeawwb; 05-18-2009 at 11:14 PM. Reason: Additional comment

  4. #4
    Forum Contributor beeawwb's Avatar
    Join Date
    01-04-2004
    Location
    Perth, WA, Australia
    MS-Off Ver
    Microsoft Office Excel 2003 (11.8146.8221) SP2
    Posts
    105

    Re: Setting Range.Formula with custom function

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1