Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
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!! -- burk ------------------------------------------------------------------------ burk's Profile: http://www.excelforum.com/member.php...o&userid=36955 View this thread: http://www.excelforum.com/showthread...hreadid=567287 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
Sub CreateFormula()
Dim CopyRange As Range Set CopyRange = Range("B2", Range("B2").End(xlDown)) Sheets("Sheet2").Range(CopyRange.Address).FormulaR 1C1 = _ "=(-1/2)*LN(1-Sheet1!RC)" End Sub HTH Die_Another_Day Post back if you need help applying the code burk wrote: 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!! -- burk ------------------------------------------------------------------------ burk's Profile: http://www.excelforum.com/member.php...o&userid=36955 View this thread: http://www.excelforum.com/showthread...hreadid=567287 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
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).FormulaR 1C1 = _ "=(-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 -- burk ------------------------------------------------------------------------ burk's Profile: http://www.excelforum.com/member.php...o&userid=36955 View this thread: http://www.excelforum.com/showthread...hreadid=567287 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
Sheets("Sheet2").Range(CopyRange.Address).FormulaR 1C1 = _
"=(-1/Inputs!" & Replace(Cells(1,ActiveCell.Column).Address(False,F alse) _ ,1,"") & ")*LN(1-Sheet1!RC)" Charles Chickering xl Geek burk wrote: 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).FormulaR 1C1 = _ "=(-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 -- burk ------------------------------------------------------------------------ burk's Profile: http://www.excelforum.com/member.php...o&userid=36955 View this thread: http://www.excelforum.com/showthread...hreadid=567287 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
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).FormulaR 1C1 = _ "=(-1/Inputs!" & Replace(Cells(1, ActiveCell.Column).Address(False False) _ , 1, "") & ")*LN(1-Sheet1!RC)" End Su -- bur ----------------------------------------------------------------------- burk's Profile: http://www.excelforum.com/member.php...fo&userid=3695 View this thread: http://www.excelforum.com/showthread.php?threadid=56728 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA code needed to apply formula to each value in a dynamic range!
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
alter code to apply to range (links cells w/ row insertions) | Excel Discussion (Misc queries) | |||
Dynamic naming of range needed | Excel Worksheet Functions | |||
dynamic formula needed | Excel Discussion (Misc queries) | |||
ranking in a dynamic range help needed?? | Excel Worksheet Functions | |||
Prompt for a range to apply code to | Excel Programming |