[Burk]

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, whereby 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(1-ValueFromSheet”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 K44, then the value fo h to be used is in cell K21 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 “Numbers” 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

Edited by Burk, 31 July 2006 - 09:34 PM.