Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Urgent Plea to all VBA Experts!!
A little mistake: Obviously, the macro quoted is used to generate th numbers in sheet "Numbers", not sheet "Calculate -- bur ----------------------------------------------------------------------- burk's Profile: http://www.excelforum.com/member.php...fo&userid=3695 View this thread: http://www.excelforum.com/showthread.php?threadid=56688 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
another sumifs plea | Excel Worksheet Functions | |||
I need help creating a score sheet with weighted values. Help Plea | Excel Worksheet Functions | |||
How can I replace zeros with blank spaces during calculations plea | Excel Worksheet Functions | |||
the autosum do not working propely ##### THAT WHAT APPEARS PLEA. | Excel Discussion (Misc queries) | |||
Beginners plea for help | Excel Programming |