Some kind of "Intersect" function for formula?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

My understanding of Excel is not the best, so please excuse me if I'm
off-track here. I want to use VBA to set and name a range around a certain
section of a workbook. I can do that. Then I want to go to the row under
the range and set a formula using VBA in each column used by the range. I
can do that, too. The formula will perform a calculation using the
intersection of the named range and that individual column. That's my
sticky point.

I can use VBA to get the column number and set a range to the intersection
of the column and the range, and the set in a formula that references that
intersected range. For certain reasons, it would be better if I didn't do
it that way. So I need to have a formula that says (for example) "AVERAGE
the numbers in the intersection of this column and rngWork1".

Can this be done? I could not find an INTERSECT function. Is there
something I'm overlooking?

Ed
 
Hi Ed

There is an Intersect Method (belongs to the Application object) which
returns a range defining the intersection of 2 (or more) ranges. Look in
the VBA help file for more information.

Regards
Paul
 
Thanks for replying, Paul. I have that Method and can use it to define a
new range for the formula. But that's not what I'm looking for. What I'm
trying to get is an equivalent from the worksheet functionality, not VBA.
For instance, if I were typing the formula in manually into the worksheet,
how would I tell the formula "use the numbers in this column only that
belong to this range"?

Ed
 
Is that range a contiguous group of cells?

If the range is multiple areas, do you want to put the formula at the bottom of
each area?

This routine will put that formula at the bottom of each area. (If the range is
contiguous, then this'll work ok, too.) won't do any harm, either.)

Option Explicit
Sub testme()

Dim myRange As Range
Dim myArea As Range
Dim myCol As Range

With Worksheets("sheet1")
Set myRange = .Range("MyRange")
For Each myArea In myRange.Areas
For Each myCol In myArea.Columns
myCol.Cells(1).Offset(myArea.Rows.Count, 0).Formula _
= "=average(" & myCol.Address & ")"
Next myCol
Next myArea
End With

End Sub
 
As a worksheet formula:

=AVERAGE((A1:A10 myRange))

Note the space between A2:A10 and myrange. And those extra parentheses are
important, too!

This formula would go into A11 (one row below the last used cell in the
column--my sample use A10).
 
Hi, Frank.

The arrangement of ranges and calculation rows is going to be entirely up to
the user, and he will determine that at run-time while he's filling this
out!

Each range *should* be a contiguous block of cells, usually 5 down by maybe
20 across. Underneath somewhere, there will be at least three rows of
different calculations for almost each column, and another three for all
ranges pooled. How to determine the calculation rows is going to be tricky.
Assuming three ranges, I can have one, two, or all three stacked on top of
each other with no breaks, giving me three rows of calculations for each
range under the last used row; or I can have a break in between any or each
of the ranges, in which case I'll have my calculation rows in between or
underneath as fits whatever the user gives me.

Then he may decide to insert or delete rows, or check a column that says
"Don't include this row." So I'll set the ranges and formulas on a
BeforeSave event (well, that's my perfect dream, of course).

Ed
(PS - I'm out for about 14 hours. I'll pick this back up when I'm back.)
 
Hi
just from the sound of this it's probably not that easy to come up with a
good formula solution. To be honest total freedom for the user is not always
possible. You'll need a kind of fixed data structure for your calculations
or you'll end up with VERY complex formulas (which are not easily to
maintain / to check).

Not everything is possible with computers and some requirements just have to
be skipped. You should try to generalise the requirements and make them
consistent for all kind of usages. e.g. always have 3 blocks (which may not
be filled), don't allow row / column insertions in every place, etc.

If you like email me a sample sheet with your requirement and I'll have a
look at it
 
Back
Top