Thank you for the example of using for each row in Rows within for each range
in Areas. I used the code almost exactly as you supplied it, and it makes my
AddTest macro much simpler, handling single, contiguous and disjoint
selections equally well, and avoids problems with the Value subscript by not
using it.

I tested the old version of the code in Excel 2003 with a selection
containing an area with 1 row and 3 columns. It didn't fail. I also
inserted your example using the MsgBox function and my xlPnumRange object
from the selection. Using Value(1,1) it gave error "Compile error: Wrong
number of arguments or invalid property assignment". Using Value2(1,1) it
displayed the specified data in the box. I still say the Excel 2003
interpreter shouldn't be giving that error for the Value property.

Thanks again for your help.

"keepITcool" wrote:



your code will stop if any area in your collections happens to
have 1 row and 2 or more columns.

MsgBox Range("a1:e1").Value2(1, 1)

will gen error number 9: subscript out of range
in ANY excel version.

THIS is much more logical and will NOT generate an error.
MsgBox Range("a1:e1").Cells(1,1).Value2



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

Thank you, keepITcool, I will try your suggestions; however, I would
like to clarify some facts:
The code I posted works correctly in both Excel 2000 and Excel 2003.
The syntax is not erroneous.
The code is cumbersome because the user may have selected a single
cell, a range of contiguous cells, or a disjoint selection containing
individual cells or several contiguous selections (or both). The
structure of range object for the selection is different in each of
these cases. (But see my final paragraph below.)
If the range contains a contiguous group of rows of one column each,
the Value2 property does not contain a one-dimensional array, but
rather a two-dimensional array. The second subscript range is 1 to
1. (I checked this again in Excel 2003 to make sure I'm reporting
the behavior accurately.) The debugger in Excel 2003 shows a Value2
property, but no Value property, although obviously it is there in
some form because the remaining references to Value work.
I still think the Excel 2003 interpreter is in error for objecting to
syntax that is not incorrect.

That said, I think I can see where iterating through the Areas
collection could make a simpler loop. I wasn't using the Areas
collection when I started writing this macro because I didn't know
how to use it then. I'll let you know how it works out.

"keepITcool" wrote:


the bug is not a bug.. imo your syntax is erroneous..


'Make next name with this permutation
Call addsingle(xlPnumRange.Value2(lPnumIndex, 1), _
strFormatSpec, strTcId & strTestType)


already part of some 'cumbersome' code to loop a multiarea range
this will very likely go bust..

if xlPnumRange is a 1 row, multicolumn range (may be 1 area of your
entire selection) the Value or Value2 of that range will be
returned as a 1 dimensional array.

dim v(1) = debug.print v(1,1) will produce an error.

Thus you might use:
Call addsingle(xlPnumRange.Cells(lPnumIndex, 1).Value, _
strFormatSpec, strTcId & strTestType)

Note that when enumerating ROWS inside a range you cannot abbreviate
getting the first cell of the row range like xlRow(1,1).. you must
use xlRow.Cells(1,1). To avoid confusion avoid the abbreviated
syntax altogether.


But even then I'd code your entire for each differently.

After your loop:

'Get selected cells
Set xlPnumRange = Selection

Dim xlArea As Range, xlRow As Range
For Each xlArea In xlPnumRange.Areas
For Each xlRow In xlArea.Rows
'Get good format spec
strFormatSpec = adjustformat(xlRow)
'Make next name with this permutation
Call addsingle(xlRow.Cells(1,1).Value, _
strFormatSpec, _
strTcId & strTestType)
Next
Next

Can't test but that should do the same as your code.


HTH

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam


LabElf wrote :

OK, for anyone who is interested, here is the code for the AddTest
macro. It calls some other macros, and makes some assumptions
about the worksheet it is called from, but I think it should be
fairly clear.


SNIP


"keepITcool" wrote:

post your code, so we may (dis)agree or advise

--
keepITcool
www.XLsupport.com | keepITcool chello nl | amsterdam