Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with cell references dependent on strings
Hi!
I'm trying to output formulas onto my worksheet but my cell references are dependent on the strings that I am using. I have used the following method to try and get over this problem, as suggested to me previously (thanks!): Code: 'Calculate the log term for each random number Const Formula As String = "=ln(1+AX^2/$D$2)" For i = 1 To n Cells(i + 1, 2) = Replace(Formula, "X", i + 1) Next i 'Sum this column Cells(n + 2, 2).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'Calculate loglikelihood and output onto the sheet Const loglikelihood As String = "=Y-2* ln((1 / Sqrt(Pi() * $D$2)) * (exp(GammaLn(($D$2 + 1) / 2) - GammaLn($D$2 / 2))) - (($D$2 + 1) / 2) * $B$Y" Cells(2, 3) = Replace(loglikelihood, "Y", n + 2) The first part works and the summation but I get an error message (run-time error '1004': Application defined or object-defined error) when I try to calculate and output the loglikelihood in a similar method. I need the 'Y+2' part to be n and the cell reference at the end of the formula to be $B$n for an n inputted by the user. Any ideas where I am going wrong or another way I can do this? Thanks! Lisa |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with cell references dependent on strings
Place "Option Explicit" at the top of your code module. Then click Debug
-Compile Does the code compile or does it complain about something not being declared... "Cardiff Maths Student" wrote: Hi! I'm trying to output formulas onto my worksheet but my cell references are dependent on the strings that I am using. I have used the following method to try and get over this problem, as suggested to me previously (thanks!): Code: 'Calculate the log term for each random number Const Formula As String = "=ln(1+AX^2/$D$2)" For i = 1 To n Cells(i + 1, 2) = Replace(Formula, "X", i + 1) Next i 'Sum this column Cells(n + 2, 2).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'Calculate loglikelihood and output onto the sheet Const loglikelihood As String = "=Y-2* ln((1 / Sqrt(Pi() * $D$2)) * (exp(GammaLn(($D$2 + 1) / 2) - GammaLn($D$2 / 2))) - (($D$2 + 1) / 2) * $B$Y" Cells(2, 3) = Replace(loglikelihood, "Y", n + 2) The first part works and the summation but I get an error message (run-time error '1004': Application defined or object-defined error) when I try to calculate and output the loglikelihood in a similar method. I need the 'Y+2' part to be n and the cell reference at the end of the formula to be $B$n for an n inputted by the user. Any ideas where I am going wrong or another way I can do this? Thanks! Lisa |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with cell references dependent on strings
Your program puts a formula in B2 that references A1. Is that what you mean?
instead of this Const Formula As String = "=ln(1+AX^2/$D$2)" For i = 1 To n Cells(i + 1, 2) = Replace(Formula, "X", i + 1) Next i Const Formula As String = "=ln(1+R[-1]C1^2/R2C4)" For i = 1 To n Cells(i + 1, 2).FormulaR1C1 = Formula Next i IF you meant B2 to refernce A2 then Const Formula As String = "=ln(1+RC1^2/R2C4)" "Cardiff Maths Student" wrote: Hi! I'm trying to output formulas onto my worksheet but my cell references are dependent on the strings that I am using. I have used the following method to try and get over this problem, as suggested to me previously (thanks!): Code: 'Calculate the log term for each random number Const Formula As String = "=ln(1+AX^2/$D$2)" For i = 1 To n Cells(i + 1, 2) = Replace(Formula, "X", i + 1) Next i 'Sum this column Cells(n + 2, 2).FormulaR1C1 = "=Sum(R2C:R[-1]C)" 'Calculate loglikelihood and output onto the sheet Const loglikelihood As String = "=Y-2* ln((1 / Sqrt(Pi() * $D$2)) * (exp(GammaLn(($D$2 + 1) / 2) - GammaLn($D$2 / 2))) - (($D$2 + 1) / 2) * $B$Y" Cells(2, 3) = Replace(loglikelihood, "Y", n + 2) The first part works and the summation but I get an error message (run-time error '1004': Application defined or object-defined error) when I try to calculate and output the loglikelihood in a similar method. I need the 'Y+2' part to be n and the cell reference at the end of the formula to be $B$n for an n inputted by the user. Any ideas where I am going wrong or another way I can do this? Thanks! Lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find and replace numeric strings in larger text strings | Excel Worksheet Functions | |||
convert relative cell references to absolute cell references via amacro? | Excel Discussion (Misc queries) | |||
How to rename references from range names to cell references | Excel Discussion (Misc queries) | |||
Furmula Dependent Cell References | Excel Discussion (Misc queries) | |||
How to find number of pairs of strings from list of strings? | Excel Worksheet Functions |