Inserting in the middle of a Formula

Posted by Maud on July 28, 2000 8:22 AM

Hi!

I'm not sure if this is impossible but if someone knows how to do this then cool!

I need a Macro that will insert a Value into the middle of a Formula.

The formula reads: =IF(ISNUMBER(H2),IF(H2&LT;=0.028,"Min",H2/A2),"")

I want a InsertBox/AddBox(?) to add the typed in text into the above formula.

The part it will be inserted is the 0.028 part.

So if I put in the InsertBox/AddBox(?) "0.01" then it will go to the formula and insert 0.01 over the existing 0.028.
So formula will now read =IF(ISNUMBER(H2),IF(H2&LT;=0.01,"Min",H2/A2),"")

The formula is in cell B1

Hope someone can help!

Maud.

Posted by Ada on July 28, 0100 8:40 AM

Maud
This may not be suitable, but you may like to consider an alternative approach (without a macro) :-

Create a name for a cell (E.G. Name = TheNumber, referring to cell B1)

Change your formula to :-
=IF(ISNUMBER(H2),IF(H2&LT;=TheNumber,"Min",H2/A2),"")

Whenever the number in cell B1 is changed, the change will automatically be reflected in the formula result.

Hope this helps.

Ada

Posted by Ada on July 28, 0100 8:43 AM

Alternatively, without creating a name the formula could be changed to :-

=IF(ISNUMBER(H2),IF(H2&LT;=H1,"Min",H2/A2),"")

Ada

Posted by Ivan Moala on July 28, 0100 1:28 PM

Maud

Try this. Let me know if it needs adj.

Sub InsertInto_Formula()
Dim Fmla As String
Dim MyVal As Double

On Error Resume Next
MyVal = Application.InputBox("Enter New value", "MyValue Input", Type:=1)
If MyVal = False Then End
Range("B1").FormulaR1C1 = _
"=IF(ISNUMBER(R[1]C[6]),IF(R[1]C[6]&LT;=" & MyVal & ",""Min"",R[1]C[6]/R[1]C[-1]),"""")"
End Sub

Ivan