Thank you very much Tom, this does the trick alright. :)


Tom Ogilvy Wrote:


sStr = Range("A2").Value
Range("B2").formula = "= HLOOKUP( RAND(), SLT_" & sStr & ", 2)"

--
Regards,
Tom Ogilvy



"Alex Hatzisavas" wrot
in
message ...

Cheers to all!

I kindly request your help on the following task, as I cannot see
to
get this going myself:

In Column B, say cell B2, I need to enter the following formul
using
VBA:

= HLOOKUP ( RAND(), << RANGE NAME DEPENDENT ON VALUE OF CELL A2
2
)

The RANGE NAME required above has already been defined with VBA code
somewhere else in the Workbook.

If Cell A2 is, for example, M10, the RANGE NAME required above has
already been defined as: SLT_M10

Thus, what I need to implement in VBA is to enter in cell B2 the
following formula:

= HLOOKUP ( RAND(), SLT_XXX, 2 ),

where XXX stands for the value of the cell A2 (say M10).

Hence, if entered correctly into cell B2, the formula would finally
be:

= HLOOKUP ( RAND(), SLT_M10, 2) , where M10 is the value of cell
A2.

(The RANGE NAME SLT_M10 has already been defined with VBA code
somewhere else in the Workbook).

I do not want to do this manually (i.e. simply type the damned
formula), since Column A (and hence, Column B) will have around 4000
rows (=4000 formulas in Column B).

I'd be very grateful to read your views on the issue, either on this
forum or at my personal e-mail:

Kind regards,
Alex


--
Alex Hatzisavas

------------------------------------------------------------------------
Alex Hatzisavas's Profile:
http://www.excelforum.com/member.php...o&userid=14559
View this thread
http://www.excelforum.com/showthread...hreadid=261928


--
Alex Hatzisava
-----------------------------------------------------------------------
Alex Hatzisavas's Profile: http://www.excelforum.com/member.php...fo&userid=1455
View this thread: http://www.excelforum.com/showthread.php?threadid=26192