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

--
Regards,
Tom Ogilvy



"Alex Hatzisavas" wrote in
message ...

Cheers to all!

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

In Column B, say cell B2, I need to enter the following formula 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