Thanx 4 the reply after all Gord!

However, isn't it possible to have the ranges themselves being inserted
instead of defining and adding and using the names?

Otherwise while re-running the macro for another data within the same book
shall have the names rng1 & rng2 be redefined leading to an error in the
first formula!

Besides the same if u go on inserting new names it shall be quite a
troublesome task finding the definition of each 1!

Please help me out!!!

--

Best Regards,
FARAZ A. QURESHI


"Gord Dibben" wrote:

I don't know how to insert two ranges in one inputbox but here is code for two
inputboxes.

Sub InsertFormula()
Dim rng1, rng2 As Range
With ActiveSheet
..Names.Add Name:="rng1", RefersTo:=Application.InputBox(prompt:= _
"Select rng1", Type:=8)
..Names.Add Name:="rng2", RefersTo:=Application.InputBox(prompt:= _
"Select rng2", Type:=8)
ActiveCell.Formula = "=sumproduct(--(rng1<0),rng2)"
End With
End Sub


Gord Dibben MS Excel MVP

On Fri, 29 Feb 2008 20:18:01 -0800, FARAZ QURESHI
wrote:

I have the following piece of code for inserting a formula on a shortcut key
instead of typing the same manually.

Sub InsertFormula()
ActiveCell.Formula = "=sumproduct(--(a1:a10<0),b1:b10)"
End Sub

Any idea how to select the ranges:
a1:a10
&
b1:b10

via "Single" Input Box (like type:=8) with 2 fields for selecting the ranges?
AND also
via "Two" separate Input Boxes for the same purpose?