user-defined fct/macro
 

it's a bit odd but i can't call a user-defined function i wrote when
try to do it from inside a sub/macro. those below wouldn't work, it'
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 tha
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
function from a macro globally available)

Cheers already

Fabaliciou

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

user-defined fct/macro
 

Use either

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

or

ActiveCell.FormulaR1C1 = FindNth(R1C1:R200C1, "A", 1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"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/

user-defined fct/macro
 

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/

user-defined fct/macro
 

that's what i thought as well but it then highlights the ":" betwee
R1C1 and R1C200 and says something like List operator expected...

grrrrrrrrr*

thanks anyway



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

user-defined fct/macro
 

Hi Bob

My last post was for Niek's answer...
I am pretty sure I tried your syntax before and it didn't work eithe
...but I must be wrong because it works fine now!

Thanks a lot

Fabaliciou

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

user-defined fct/macro
 

As you just want to return the value, you don't need FormulaR1C1, you can
use .Value


--

HTH

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

"fabalicious " wrote in message
...
Hi Bob

My last post was for Niek's answer...
I am pretty sure I tried your syntax before and it didn't work either
..but I must be wrong because it works fine now!

Thanks a lot

Fabalicious


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

user-defined fct/macro
 

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/

user-defined fct/macro
 

AARRRGGHH! Forgot the quotes! And Equals-signs!

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

or

ActiveCell.FormulaR1C1 = "=FindNth(R1C1:R200C1, ""A"", 1)"

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Niek Otten" wrote in message
...
Use either

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

or

ActiveCell.FormulaR1C1 = FindNth(R1C1:R200C1, "A", 1)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

"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/

user-defined fct/macro
 

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/

user-defined fct/macro
 

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/

user-defined fct/macro
 

Hi Bob and Niek

it's like Bob said. If i write it in brackets the worksheet can'
execute the function because it's not globally available but only i
the macro. if i use

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

the macro executes the function and it works, so that's what i am usin
now.

anyway, thanks to both of you

Fabaliciou

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

user-defined fct/macro
 

Right, Bob!

Niek
"fabalicious " wrote in message
...
Hi Bob and Niek

it's like Bob said. If i write it in brackets the worksheet can't
execute the function because it's not globally available but only in
the macro. if i use

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

the macro executes the function and it works, so that's what i am using
now.

anyway, thanks to both of you

Fabalicious


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