And with mgs73's suggestions added:
Option Explicit
Sub Test()
Range("J2:J4").Value = Evaluate("=apple+pear")
Range("J7").Value = Evaluate("=sue apple")
Debug.Print Range("apple").Address
Debug.Print Range("orange").Address
Debug.Print Range("pear").Address
Range("K2:K4").Value = Evaluate("=" & Range("apple").Address & "+" & Range("pear").Address)
Range("K7").Value = Evaluate("=" & Range("sue").Address & " " & Range("apple").Address)
Debug.Print Intersect(Range("john"), Range("apple")).Value
Debug.Print Intersect(Range("john"), Range("apple")).Value + Intersect(Range("john"), Range("pear")).Value
Debug.Print Application.WorksheetFunction.Sum(Intersect(Range("john"), Union(Range("apple"), Range("pear"))))
End Sub
Bookmarks