Enter simulation-related HLOOKUP formula in cells
 

Cheers to all!

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

In Column B, say cell B2, I need to enter the following formula usin
VBA:

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

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

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

Thus, what I need to implement in VBA is to enter in cell B2 th
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 finall
be:

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

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

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

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

Kind regards,
Ale

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

Enter simulation-related HLOOKUP formula in cells
 

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