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

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

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