ExcelBanter - Urgent Plea to all VBA Experts!!

Need urgent help on the following issue, where I have to come up with a
very difficult macro (never written one!)

I have three excel sheets:
Sheet “Inputs”, Sheet “Numbers”, and Sheet “Calculate”

What is supposed to happen:
-Sheet Numbers contains an array of numbers, extending from columns B
to m & from rows 2 to k + 1, wherey m & k are specified in sheet
"Inputs" (I have this array already & the Macro generating this array
in sheet "Numbers"!-see bottom of this post)
-Sheet “Calculate” is supposed to transform each value of the array in
Sheet Numbers through the following formula:
‘=-1/h*LN(1ValueFromSheet”Numbers),
whereby the respective value h is also specified in SheetInputs, row 21
(see below)
-Sheet Inputs specifies the exact extend of the array in Sheet”Numbers”
by specifying the m & the k:
Cell B2 in sheet “Inputs” contains a column label (e.g. W) which
specifies the m
Cell B3 in sheet “Inputs” contains a number which specifies the k (i.e.
the row number of the array in Sheet “Calculate”)
For instance, if cell B2 in Sheet “Inputs” contains the letter ‘W’ and
cell B3 in sheet "Inputs" contains the number 220, then the array of
numbers in sheet “Calculate” will extend from B2 to W221 (because the
row number is defined as k+1)
-Moreover, row 21 in sheet “Inputs” contains (from column B to FY) the
values for h which are supposed to be used in the formulas in the
corresponding columns in sheet “Calculate”
-As mentioned above, sheet “Calculate” is supposed to transform each
value of sheet “Numbers” according to the formula
=(-1/h*LN(1-ValueFromSheetNumbers)), whereby the “h” comes from
row 21 in sheet “Inputs” (the column is supposed to be the same as
the column of the cell in Sheet Calculate that is being calculated,
i.e. if the cell to be calculated in sheet “Calculate” is cell *K*44,
then the value fo h to be used is in cell *K*21 in sheet “Input)
For instance, cell J52 in sheet “Calculate” shall contain the results
of the formula: (-1/Inputs!J$21)*LN(1-Numbers!J52)
Likewise, cell Y119 shall contain the formula
=(-1/Inputs!Y$21)*LN(1-Numbers!Y119)

Note:
- the cell reference to be used from sheet "Numbers" is the very same
as the cell to be calculated in sheet "Calculate" (e.g. Y119)
- the cell reference in sheet "Inputs" containing the relevant value
for the h has the same column label (e.g. Y) as the cell to be
calculated in sheet "Calculate" (i.e. Y), whereas the row # is always
21

-at the end of the Macro, sheet “Calculate” is supposed to contain an
array of the same extend as that in sheet Numbers (i.e. from B2 to
cell(m,i+1), whereby each of the values in sheet “Numbers” has been
transformed according to the abovementioned formula

BTW: the numbers in sheet “Calculate” are generated using the following
Macro

Sub Random()
Sheets("Inputs").Calculate
m = Sheets("Inputs").Range("B2").Value
k = Range("Inputs!B3").Value
For i = 2 To k + 1
Range("B" & i & ":" & m & i).FormulaArray =
"=TRANSPOSE(RandBM(Inputs!R2C2))"
Next i

End Sub

Many thanx to everyone who takes the time to reply!!

Burki

--
burk
------------------------------------------------------------------------
burk's Profile: http://www.excelforum.com/member.php...o&userid=36955
View this thread: http://www.excelforum.com/showthread...hreadid=566888