Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is typed
into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy

I don't believe there is a direct equivalent in VBA for INDIRECT. However,
you can use Offset to refer other cells.

myValue = Range("B2").Offset(0, -1)

This would give you the value in Cell A2.



"H.G. Lamy" wrote in message
...
Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is
typed into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy

You can refer to the range referenced in A1 with Range(Range("A1").Value),
for example...

Set DefinedNameRange = Range(Range("A1").Value)

or...

MsgBox Range(Range("A1").Value).Address

--
Rick (MVP - Excel)


"H.G. Lamy" wrote in message
...
Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is
typed into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy

Thanks you !

hgl

"Rick Rothstein" wrote in message
...
You can refer to the range referenced in A1 with Range(Range("A1").Value),
for example...

Set DefinedNameRange = Range(Range("A1").Value)

or...

MsgBox Range(Range("A1").Value).Address

--
Rick (MVP - Excel)


"H.G. Lamy" wrote in message
...
Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is
typed into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy

I don't think your formula below would work correctly. I guess you mean
a formula like this.

=INDEX(INDIRECT($A$1),INT((RAND()*COUNTA(INDIRECT( $A$1))+1)))

If this is the case, the UDF equivalent to the formula above is

Function myfunc(rng As Range) As Variant
Dim R As Range
Set R = Evaluate(rng.Value)
With Application
myfunc = .Index(R.Value, Int((Rnd() * .CountA(R.Value)) + 1))
End With
End Function

or following Rick's example,

Function myfunc(rng As Range) As Variant
Dim R As Range
Set R = Range(rng.Value)
With Application
myfunc = .Index(R.Value, Int((Rnd() * .CountA(R.Value)) + 1))
End With
End Function

Keiji

H.G. Lamy wrote:
Hello,

by this:

=INDEX(INDIRECT($A$1),(RAND()*COUNTA(INDIRECT($A$1 )))+1)

I often pick random elements from a list (named range), whose name is typed
into $A$1.

However, the attempt to make this a UDF function in VBA fails, since
"INDIRECT()" is not part of the "worksheetfunction"-collection in VBA.

Is there any equivalent that may do the trick ?

Thank you in advance.

Kind regards,

H.G. Lamy

too complicated. If you have a named range JOEL on the worksheet


A = "Joel"
set Myrange = Range(A)



the named range is a string like Range("JOEL"). So you can put any
valid sting inside the Range() function that matches the name in the
worksheet. You can even do his

A = "JO"
B = "EL"
Set MyRange = Range(A & B)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help

Thank you very much !

hgl

"joel" wrote in message
...

too complicated. If you have a named range JOEL on the worksheet


A = "Joel"
set Myrange = Range(A)



the named range is a string like Range("JOEL"). So you can put any
valid sting inside the Range() function that matches the name in the
worksheet. You can even do his

A = "JO"
B = "EL"
Set MyRange = Range(A & B)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help

Hi, iam new to VBA but have a similar dilema. How would i create a UDF to
replace the following mega formula. The formula is part of an intermediate
sheet that cast % of budgets to consolidation pages. I have several of these
transfer sheets so the SS size balloons.

=INDIRECT(ADDRESS(CELL("row",M12),CELL("col",M12), ,,$A$3))*INDIRECT(ADDRESS(CELL("row",M12),$E$3,,,$ A$3))*INDIRECT(ADDRESS($F$3,CELL("col",M12),,,$A$3 ))

The inputs are $a$3 the sheetname where data is $A$3 the column number where
the % lives $F$3 the number of rows in that sheet

Help

"H.G. Lamy" wrote:

Thank you very much !

hgl

"joel" wrote in message
...

too complicated. If you have a named range JOEL on the worksheet


A = "Joel"
set Myrange = Range(A)



the named range is a string like Range("JOEL"). So you can put any
valid sting inside the Range() function that matches the name in the
worksheet. You can even do his

A = "JO"
B = "EL"
Set MyRange = Range(A & B)


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help



.

I think something simple like this

Function IndirectSht(Target As Range, ShtName As String)
InDirectSht = Sheets(ShtName).Range(Target.Address)
End Function


Call with
=IndirectSht(A6,"sheet2")


The function will return the data in another sheet at the same range
that is specified on the current sheet.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help

Legend!

i ended up writing this which ismy first UDF. Cheers

Function RoboCell(Curcell As Range, ShtName As String, ColNum As Integer, _
RowNum As Integer)

'returns the value of the current cell location in another sheetname
'multiplied by the % in colnum * % in rownum

RoboCell = Sheets(ShtName).Range(Curcell.Address) * _
Sheets(ShtName).Cells(Curcell.Row, ColNum) * _
Sheets(ShtName).Cells(RowNum, Curcell.Row)

End Function

"joel" wrote:


I think something simple like this

Function IndirectSht(Target As Range, ShtName As String)
InDirectSht = Sheets(ShtName).Range(Target.Address)
End Function


Call with
=IndirectSht(A6,"sheet2")


The function will return the data in another sheet at the same range
that is specified on the current sheet.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help

.

One further question,

The Calculation speed by replacing the large Formulae to the UDF decreased
exponentially. ie a nano second to 20 seconds.

Is this a general inefficiency of UDF's or is there something i have done
wrong in the code? I am only pasting this function on a 200x24 grid!

"joel" wrote:


I think something simple like this

Function IndirectSht(Target As Range, ShtName As String)
InDirectSht = Sheets(ShtName).Range(Target.Address)
End Function


Call with
=IndirectSht(A6,"sheet2")


The function will return the data in another sheet at the same range
that is specified on the current sheet.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157454

Microsoft Office Help

.