Input Boxes 4 Inserting A Range In A Formula

  • Thread starter Thread starter FARAZ QURESHI
  • Start date Start date
F

FARAZ QURESHI

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?
 
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
 
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!!!
 
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
 
Thanks.

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


Gord
 
Back
Top