Outputting a formula in a cell using VB

[Guest]

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!

 

[Tushar Mehta]

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