PDA

View Full Version : intersect function



lior03
03-31-2007, 11:46 PM
hello
i want excel to sum cells of two ranges ,only if they intersect.
if the do not i want excel to present a msgbox

Dim isect
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
rng1 = Application.InputBox("select first range", Type:=8)
rng2 = Application.InputBox("select second range", Type:=8)
Set isect = Application.intersect(Range("rg1"), Range("rg2"))
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
End If
End With
ActiveCell.FormulaArray = "=Application.WorksheetFunction.Sum(rng1, rng2))"


thanks

mdmackillop
04-01-2007, 12:19 AM
Do you mean the sum of the cells in the intersection, or all cells of the union of the ranges?

lior03
04-01-2007, 12:30 AM
hello
the sum of the intersection.
thanks

Bob Phillips
04-01-2007, 03:58 AM
Dim isect
Dim rng1 As Range
Dim rng2 As Range
With ActiveSheet
Set rng1 = Application.InputBox("select first range", Type:=8)
Set rng2 = Application.InputBox("select second range", Type:=8)
Set isect = Application.Intersect(rng1, rng2)
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
End If
End With
If isect Is Nothing Then
MsgBox "Ranges do not intersect"
Else
ActiveCell.Formula = "=SUM(" & isect.Address & ")"
End If

Norie
04-01-2007, 05:53 AM
Lior

Why are you using FormulaArray to try and put an Application.WorksheetFunction in a cell?