LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 5,934
Default How to work with reference unions (A1,A3,A5)?

I would use a ParamArray to process the comma
delimited ranges and then iterate each range
individually, something like this...
Function Test(ParamArray R() As Variant)


Forgive me, but I don't see how that addresses my question.

I specifically asked about passing a reference union of the form
(A1,A3,A5).
The issue is determining its size.

.....<snip

I was wondering if a parameter of the form (A1,A3,A5) is a different type
of
object than A1:A5. So if I declared myVal as Variant instead of Range,
which I should do anyway for an unrelated reason, perhaps I could
distinguish a reference union in some way programmically (e.g. like
TypeName, but I know that does not do the trick), and some property of
that object would give me its size. As I noted previously,
myVal.Rows.Count does not suffice.


Sorry, but I totally missed the point of your question when I first read
your message. I think you may be looking to count the areas, not the rows.
Given this function declaration...

Function myIRR(myVal as Variant, Optional myGuess as Double = 0.1)

consider this...

NumberOfRanges = myVal.Areas.Count

This would properly count three even if one of the ranges was a contiguous
range reference. For example, if the union was (A1,A4:A7,A9), the area count
would still be 3 even though the total number of cells is 6. You can iterate
the areas by index value. For example...

For N = 1 To myVal.Areas.Count
Debug.Print myVal.Areas(N).Address
Next

Is this what you were looking for?

--
Rick (MVP - Excel)



 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
unions, intersections or array constants Loadmaster Excel Worksheet Functions 24 May 6th 09 08:11 PM
formula to work in a 3-d reference with 2 work books capt c Excel Worksheet Functions 1 April 8th 09 08:04 PM
Help.. Drag reference to the right does not work... [email protected] New Users to Excel 11 December 5th 06 10:25 AM
Advanced Filters with Unions John Excel Programming 1 April 27th 06 01:34 PM
Sorting Unions Alex[_9_] Excel Programming 1 September 4th 03 07:40 PM


All times are GMT +1. The time now is 12:00 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"