Outputting a formula in a cell using VB
 

Hi!
I am using VB to maximise a series of functions using the solver Add-In.
Here is a part of my code:

logsum = 0
For l = 1 To n
'Set the initial value of v to 1:
Cells(k + 1, 4) = 1
logsum = logsum + Log(1 + ((Cells(l + 1, 1).Value) ^ 2) / Cells(k + 1,
4).Value)
Next l
loglikelihood = n * Log((1 / (WorksheetFunction.Pi * Cells(k + 1, 4).Value)
^ (1 / 2)) * (Exp(WorksheetFunction.GammaLn((Cells(k + 1, 4).Value + 1) / 2)
- WorksheetFunction.GammaLn(Cells(k + 1, 4).Value / 2)))) - ((Cells(k + 1,
4).Value + 1) / 2) * logsum
Cells(k + 1, 3) = loglikelihood
SolverOk SetCell:="$k+1$3", MaxMinVal:=1, ByChange:="$k+1$4"
SolverAdd CellRef = "$k+1$4", Relation:=3, FormulaText:="0.000001"
SolverSolve UserFinish:=True
Next k

My problem is that I want to maximise the "loglikelihood" with respect
to the value given in cell(k+1,4) but at the moment I am outputting a number
in Cell(k+1,3) not the formula that is used to calculate this number so
Solver does not work. Does anyone have any suggestions how I could output the
formula in the cells instead of the value obtained at the end?

Thanks!

Outputting a formula in a cell using VB
 

To get the correct syntax to enter a formula into a cell, turn on the
macro recorder (Tools | Macro Record new macro...), enter the formula
by hand, and turn off the recorder. XL will give you the necessary
code. You can then generalize this by replacing specific cell
addresses with addresses created programmatically.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Hi!
I am using VB to maximise a series of functions using the solver Add-In.
Here is a part of my code:

logsum = 0
For l = 1 To n
'Set the initial value of v to 1:
Cells(k + 1, 4) = 1
logsum = logsum + Log(1 + ((Cells(l + 1, 1).Value) ^ 2) / Cells(k + 1,
4).Value)
Next l
loglikelihood = n * Log((1 / (WorksheetFunction.Pi * Cells(k + 1, 4).Value)
^ (1 / 2)) * (Exp(WorksheetFunction.GammaLn((Cells(k + 1, 4).Value + 1) / 2)
- WorksheetFunction.GammaLn(Cells(k + 1, 4).Value / 2)))) - ((Cells(k + 1,
4).Value + 1) / 2) * logsum
Cells(k + 1, 3) = loglikelihood
SolverOk SetCell:="$k+1$3", MaxMinVal:=1, ByChange:="$k+1$4"
SolverAdd CellRef = "$k+1$4", Relation:=3, FormulaText:="0.000001"
SolverSolve UserFinish:=True
Next k

My problem is that I want to maximise the "loglikelihood" with respect
to the value given in cell(k+1,4) but at the moment I am outputting a number
in Cell(k+1,3) not the formula that is used to calculate this number so
Solver does not work. Does anyone have any suggestions how I could output the
formula in the cells instead of the value obtained at the end?

Thanks!