formula through inputbox

[Utkarsh]

Instead of entering a formula like =IF(ISERROR(C1/D1),"",C1/D1), I
would like the user the enter the formula like c1/d1 through an
inputbox. The code below tries to do this but is not working - the
results looks like =IF(ISERROR(y),"",y)


Sub Macro1()

y = InputBox("enter formula")

ActiveCell.Formula = "=IF(ISERROR(y),"""",y)"
End Sub

 

[Peter T]

Two ways, uncomment the second commented line to try the second way

Sub test()
Dim sFmla As String
Dim y As Variant

y = InputBox("enter formula")
If VarType(y) <> vbBoolean Then ' user cancelled

sFmla = Replace("=IF(ISERROR(#~#),"""",#~#)", "#~#", y)

' sFmla = "=IF(ISERROR(" & y & "),""""," & y & ")"

ActiveCell.Formula = sFmla
End If

End Sub

Regards,
Peter T

 

[OssieMac]

If you use the Application.InputBox method (check it out in help; it is
different then InputBox Function) then you can set the Type to zero which
allows formula input.

ActiveCell = Application.InputBox(Prompt:="Enter formula", Type:=0)

With above you can enter =IF(ISERROR(C1/D1),"",C1/D1) directly into the
Application.InputBox

 

[Peter T]

I think, or rather guess as it's not clear at all, the OP's objective is
simply to enter [say] "C1/D1" without quotes as the variable element of the
long formula which eventually needs to be applied to the cell's formula. If
so both the VBA and Excel Inputbox would work fine.

Regards,
Peter T

 

[OssieMac]

Re-reading the question I think you are probably correct Peter.

In which case using Application.InputBox the Type should be 2 for a string
and then use one of your methods to create the formula final formula.

Dim y As String

y = Application.InputBox(Prompt:="Enter Formula", Type:=2)

ActiveCell.Formula = "=IF(ISERROR(" & y & "),""""," & y & ")"