How to enter value of a formula in vba

  • Thread starter Thread starter shternm
  • Start date Start date
S

shternm

Please help I am not sure why .Value does not work for me.

Do While ActiveCell.Offset(0, 1).Value <> Empty
ActiveCell*.Value* = "=If(R[0]C[1]<R[0]C[2],R[0]C[1]&"
""&R[0]C[2],R[0]C[2]&"" ""&R[0]C[2])"
ActiveCell.Offset(1, 0).Select
Loo
 
Try

ActiveCell.Formula = "=If(R[0]C[1]<R[0]C[2],R[0]C[1]&""
""&R[0]C[2],R[0]C[2]&"" ""&R[0]C[2])"

Regards,
Alan.
 
That doesn't work either.

Just so I am making myself clear: I don't want to see the formula in
the cell just a result (value).
 
Apologies,
I didn't look carefully enough at your query. I see now what you mean. I
would repost this in 'Programming', The VB experts are there although I'm
not sure this can be done directly in code, it may have to refer to a
worksheet formula, I'll look forward to the answers, Good Luck,
Regards,
Alan.
 
Maybe activecell.formular1c1 would work.
Please help I am not sure why .Value does not work for me.

Do While ActiveCell.Offset(0, 1).Value <> Empty
ActiveCell*.Value* = "=If(R[0]C[1]<R[0]C[2],R[0]C[1]&""
""&R[0]C[2],R[0]C[2]&"" ""&R[0]C[2])"
ActiveCell.Offset(1, 0).Select
Loop
Click to expand...
 
Try:

With ActiveCell
.FormulaR1C1 = "=If(R[0]C[1]<R[0]C[2],R[0]C[1]&""""&" _
& "R[0]C[2],R[0]C[2]&"" ""&R[0]C[2])"
.Value = .Value
End With

Hope this helps
Rowan
 
The code inserts the formula into the cell and then replaces the formula
with the value of the cell. So it works a bit like typing in the formula
and then doing a copy > pastespecial values over the formula.

Regards
Rowan
 
Back
Top