intersect function

[lior03]

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
[VBA]
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))"

[/VBA]
thanks

[mdmackillop]

Do you mean the sum of the cells in the intersection, or all cells of the union of the ranges?

[lior03]

hello
the sum of the intersection.
thanks

[Bob Phillips]

[vba]

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
[/vba]

[Norie]

Lior

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