Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2004
    Posts
    11

    Unanswered: syntax problem / function call in sub

    it's a bit odd but i can't call a user-defined function i wrote when i try to do it from inside a sub/macro. those below wouldn't work, it's just a syntax problem i reckon:

    ActiveCell.FormulaR1C1 = FindNth(A1:A200, "A", 1)
    ActiveCell.FormulaR1C1 = FindNth("A1:A200", "A", 1)
    etc.

    i can call it from within a worksheet with

    = FindNth(A1:A200, "A", 1)

    when typing this into a cell.

    but when i use it in the macro, the function is only available to that module and not to the worksheet the macro has been applied on....
    For the same reason, i cannot use sth like

    ... = "=FindNth(A1:A200, "A", 1)"

    Could somebody give me the right syntax (or tell me how to make a function from a macro globally available)

    Cheers already

    Fabalicious

  2. #2
    Join Date
    Oct 2003
    Posts
    1,091
    I think you have to include the sheet name with it.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  3. #3
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    If you have defined the function in a Sheet or ThisWorkbook module try
    putting it in a separate code mode. This seems to work !

    MTB

  4. #4
    Join Date
    Mar 2004
    Location
    Fort Worth, Texas, USA
    Posts
    68
    It's a bear putting double-quotes inside strings, but I think this is what you want:

    ActiveCell.FormulaR1C1 = "=FindNth(A1:A200, ""A"", 1)"
    'note the double double-quotes around A

Posting Permissions

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