Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Urgent Plea to all VBA Experts!


  • Please log in to reply

#1
Burk

Burk

    New Member

  • Member
  • Pip
  • 2 posts
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.

  • 0

Advertisements







Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP