I have the UDF Test(rng as range) and I want to do something like this:
Dim rng2 as Range
Set rng2 = blah blah
rng2.formula = "=test(rng)"
How can I do this?
Add a comment
|
3 Answers
What if rng is on another sheet? Then you need this:
rng2.Formula = "=test('" & rng.Parent.Name & "'!" & rng.Address & ")"
5 Comments
Matteo NNZ
I would definitely choose this answer.
Matteo NNZ
Just an observation: if the OP wants to insert the function via code, probably he doesn't refer to any
ActiveSheet in particular. I would remove the If check and simply refer to the parent names of both rng (input) and rng2 (output).
SierraOscar
@MatteoNNZ wouldn't be any point for
rng2 as we're dealing with the object directly - agreed there's no need for the ActiveSheet check as the address will still work with the sheet name even if it's on the same sheet so can be reduced to just one IIf() statement. Will update when I get back to a computer!Matteo NNZ
Right for the rng2, I agree with this simple change you said.
Ilias
@S O Yep this works as a charm.I used this just before I saw your edit.Thumbs up.
The range address must be passed as a variable, not a string:
Dim rng2 as Range
Set rng2 = blah blah
rng2.formula = "=test(" & rng.Address & ")"
Comments
You have to specify the address of range i.e :
rng2.formula = "=test(" & rng.address & ")"
Comments
