Bob,

You may well be right. Hopefully he writes back to let us know what solved
the problem!

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
Niek,

I am not sure you are right. The OP posted that it works, and he did say
that he wants to call the function from a sub/macro, not that he wants to
write it as a formula.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Niek Otten" wrote in message
...
Bob,

Not true in this case; the OP writes a formula to a worksheet so it
should
have a worksheet format

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"Bob Phillips" wrote in message
...
VBA is not a worksheet, and has different syntax rules. If you want to
address a range, you have to tell VBA

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


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"fabalicious " wrote in
message
...
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


---
Message posted from http://www.ExcelForum.com/