user-defined fct/macro

F

fabalicious

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
 
N

Niek Otten

Use either

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

or

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
 
B

Bob Phillips

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

fabalicious

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
 
F

fabalicious

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
 
B

Bob Phillips

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

Niek Otten

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 said:
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)
Click to expand...
 
N

Niek Otten

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 said:
Use either

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

or

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

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel
Click to expand...
 
B

Bob Phillips

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 said:
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
Click to expand...
 
N

Niek Otten

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
 
F

fabalicious

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
 
Top