Sheets("Sheet2").Range(CopyRange.Address).FormulaR 1C1 = _
"=(-1/Inputs!R21C[0])*LN(1-Sheet1!RC)"
Try that. Let me know if that works please.

Charles Chickering
xl Geek

burk wrote:
Hi,

Many thanx again for taking the time to reply!

I tried your suggestion but it didnt quite work out yet
Basically, there were two Problems, both related to picking the correct
cell reference for the Expression (-1/ValueFromSheetInputs!)

1) The macro does not insert any row number, which should be 21 for
each cell in Sheet2 (because all the values to be inserted are in Sheet
Inputs, row 21, column B-FY)

As it stands now, the Macro merely inserts an alphabetical character
(so that the formulas inserted by the macro in Sheet2 are of the kind:
(-1/Inputs!C)*LN(...)

2) The column label does not adjust according to the cell, but is the
same for the entire array
In fact which column label (i.e. B, D, AG etcg) is actually inserted in
the expression depends on which cell in Sheet2 (i.e. the Sheet in which
the output of the Macro is supposed to be inserted) is klicked on
BEFORE I start the Macro..

For example, if (before starting the macro) Cell C35 is activated, the
expression in the formula will be (-1/Inputs!C)*LN(...)


I would hugely appreciate if any of you could help me to fix these two
last problems

PS.The macro which I tried was that:

Sub CreateFormula3()
Dim CopyRange As Range
m = Sheets("Inputs").Range("D3").Value
k = Range("Inputs!$C$14").Value
Set CopyRange = Range("B2" & ":" & m & k + 1)
Sheets("Sheet2").Range(CopyRange.Address).FormulaR 1C1 = _
"=(-1/Inputs!" & Replace(Cells(1, ActiveCell.Column).Address(False,
False) _
, 1, "") & ")*LN(1-Sheet1!RC)"
End Sub


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