ExcelBanter - Formula in Cell with VBA

Hi sebastien,

thanks for your suggestion. The FormulaLocal worked perfectly.

Regards,

Khlaudhya

"sebastienm" wrote:

I believe you should use FormulaLocal instead of Formula.
'-------------------------------------------------------
Dim Rg as Range
Dim s as string

For j = 1 To 8

Set Rg= Range("Prices").Columns(j)

If j < mMinRental Then
Rg.Value = 0
ElseIf j < 8 Then
'Use Application.InputBox instead. More flexible. <-------
s = Application.InputBox("How do we calculate " & j & "
prices?", "Formulas", rg.Cells(1).FormulaLocal, , , , , 0)
'Check if user cancelled <-----------
If s = "False" Then 'user cancelled
'nothing or whatever you want
Else
Rg.FormulaLocal = s
End If
Else
'what if NOT (j<mMinRental OR j<8)
End if
'---------------------------------------------------
Regards,
Seb

"Khlaudhya" wrote:

Hi,

I have the following code in my project:

For j = 1 To 8

Range("Prices").Columns(j).Select

If j < mMinRental Then
Range("Prices").Columns(j).Cells.Value = 0
ElseIf j < 8 Then Range("Prices").Columns(j).Cells.Formula = InputBox("How do we calculate " & j & " prices?", "Formulas")
End If

This is meant for the user to input the formula with which the prices for a given group of products will be calculated.

When the inputbox result is, for example, =Seven/2 (Seven being a range in the worksheet) this works marvellously, but if the input is, for example, =MROUND(Seven/2;0,1) this results in an error: "Application-defined or Object-defined error".

Can someone help me please?

Thank you,

Khlaudhya