VBA code needed to apply formula to each value in a dynamic range!

[burk]

Hi to all VBA experts!

Unfortunately, I am an entire Novice to VBA, and I am faced with the
following task:

In Sheet1 I have an array of numbers between 0 & 1; The problem is that
this array is of -varying -size (extending from B2 over a varying # of
rows & columns)...and I want to apply the following formula to each
value in this array: (-1/2)*LN(1-ValueFromSheet1)

The corresponding output shall be displayed in the subsequent Sheet2
(from B2 onwards); logically, the output will be another array of the
same size as the one in Sheet1 (since for each value in Sheet1 I will
have an output value in Sheet2)

Ideally, the output array will be displayed in the same cells as the
input array, i.e. starting from B2 & cover the same rows & columns in
Sheet2
Sheet2


Any ideas of how to go about it are very much appreciated!!

 

[Die_Another_Day]

Sub CreateFormula()
Dim CopyRange As Range
Set CopyRange = Range("B2", Range("B2").End(xlDown))
Sheets("Sheet2").Range(CopyRange.Address).FormulaR1C1 = _
"=(-1/2)*LN(1-Sheet1!RC)"
End Sub

HTH

Die_Another_Day

Post back if you need help applying the code

 

[burk]

Hi Dad

many thanx for your reply; it was indeed very helpful....I modified it
somewhat so that it worked, in the following form:

Sub CreateFormula2()
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).FormulaR1C1 = _
"=(-1/2)*LN(1-Sheet1!RC)"
End Sub


I have one more question left:

Insteadt of the dividing the -1 by 2, I need the -1 divided by a value
from another sheet, called Inputs.xls

that is, row 21 in sheet inputs contains a row of 180 possible divisors
(in cells B21 to FY21)...the twist is now that the formula shall select
the divisor such that the column label of the cell in Sheet2 & the
divisor are identical...

For instance, the Macro should insert the following formula into cell
D53 of Sheet2: =(-1/D21)*LN(1-Sheet1!D53)

& in cell X127 of Sheet2, the macro shall insert the following formula:

=(-1/X21)*LN(1-Sheet1!X127)

As I said, the bit with Sheet1 works fine using the formula above; all
I need is the Macro to insert the right divisor from
Range(Inputs!B21:Inputs!FY21) depending on the column of the respective
cell in Sheet3, as described above

 

[Die_Another_Day]

Sheets("Sheet2").Range(CopyRange.Address).FormulaR1C1 = _
"=(-1/Inputs!" &
Replace(Cells(1,ActiveCell.Column).Address(False,False) _
,1,"") & ")*LN(1-Sheet1!RC)"

Charles Chickering
xl Geek

 

[burk]

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 correc
cell reference for the Expression (-1/ValueFromSheetInputs!)

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

As it stands now, the Macro merely inserts an alphabetical characte
(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 th
same for the entire array
In fact which column label (i.e. B, D, AG etcg) is actually inserted i
the expression depends on which cell in Sheet2 (i.e. the Sheet in whic
the output of the Macro is supposed to be inserted) is klicked o
BEFORE I start the Macro..

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


I would hugely appreciate if any of you could help me to fix these tw
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).FormulaR1C1 = _
"=(-1/Inputs!" & Replace(Cells(1, ActiveCell.Column).Address(False
False) _
, 1, "") & ")*LN(1-Sheet1!RC)"
End Su

 

[Die_Another_Day]

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

Charles Chickering
xl Geek