Input Boxes 4 Inserting A Range In A Formula
 

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

Input Boxes 4 Inserting A Range In A Formula
 

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?

Input Boxes 4 Inserting A Range In A Formula
 

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?

Input Boxes 4 Inserting A Range In A Formula
 

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

Input Boxes 4 Inserting A Range In A Formula
 

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

Input Boxes 4 Inserting A Range In A Formula
 

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