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?

--
Best Regards,

FARAZ A. QURESHI

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?

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?

Good point about stacking up range names.

This seems to test out OK.

Sub InsertFormula()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Application.InputBox(prompt:="Select rng1", Type:=8)
Set rng2 = Application.InputBox(prompt:="Select rng2", Type:=8)
ActiveCell.Formula = "=SUMPRODUCT(--(" _
& rng1.Address & "<0)," & rng2.Address & ")"
End Sub


Gord

On Sat, 1 Mar 2008 13:10:00 -0800, FARAZ QURESHI
wrote:

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!!!

Thanx Gord!

XCLent 1!!!

--

Best Regards,
FARAZ A. QURESHI


"Gord Dibben" wrote:

Good point about stacking up range names.

This seems to test out OK.

Sub InsertFormula()
Dim rng1 As Range
Dim rng2 As Range
Set rng1 = Application.InputBox(prompt:="Select rng1", Type:=8)
Set rng2 = Application.InputBox(prompt:="Select rng2", Type:=8)
ActiveCell.Formula = "=SUMPRODUCT(--(" _
& rng1.Address & "<0)," & rng2.Address & ")"
End Sub


Gord

On Sat, 1 Mar 2008 13:10:00 -0800, FARAZ QURESHI
wrote:

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!!!

Thanks.

Now you have to work on returning two ranges from a single inputbox!


Gord

On Sun, 2 Mar 2008 10:04:00 -0800, FARAZ QURESHI
wrote:

Thanx Gord!

XCLent 1!!!