RelearningExcel
New Member
- Joined
- Jun 29, 2012
- Messages
- 14
Hello all,
Using Name Manager I have defined a dynamic range that references a particular column. The named range grows/shrinks as data is entered into the column, and the data can be alpha or numeric. Also, if a blank cell appears in the dataset, the height of the named range is unaffected.
The user is allowed to edit the list but I also update the list with VBA as follows
Range("MyDynamicRange")(IndexVal)= "Test Value"
where IndexVal represents the row that requires updating.
From the immediate window the following VBA returns the appropriate reference for range MyDynamicRange
gives
=OFFSET('Criteria Lists'!$B$2,0,0,MAX(IFERROR(MATCH("*",'Criteria Lists'!$B:$B,-1),0),IFERROR(MATCH(9.99999999999999E+307,'Criteria Lists'!$B:$B,1),0))-1,1)
Also, it is legitimate for the range to be empty.
In this case Range("MyDynamicRange")(IndexVal)= "Test Value" will return err 1004.
So in the case where my named range is clear and I want to populate the first entry I have to explicitly populate cell $B$2.
After that the Named Range has data and the code will work.
I don't want to hard code the reference "$B$2" in my VBA as in
So finally, here's the question....
Is it possible to return the column reference of an empty dynamic range without parsing the return value of the RefersTo property of the Names object
I've tried
which is ok if the range has data but returns err 1004 if it is blank
Sorry if my explanation is a bit long winded and thanks in advance,
Bill
Using Name Manager I have defined a dynamic range that references a particular column. The named range grows/shrinks as data is entered into the column, and the data can be alpha or numeric. Also, if a blank cell appears in the dataset, the height of the named range is unaffected.
The user is allowed to edit the list but I also update the list with VBA as follows
Range("MyDynamicRange")(IndexVal)= "Test Value"
where IndexVal represents the row that requires updating.
From the immediate window the following VBA returns the appropriate reference for range MyDynamicRange
?activeworkbook.Names("MyDynamicRange").RefersTo
gives
=OFFSET('Criteria Lists'!$B$2,0,0,MAX(IFERROR(MATCH("*",'Criteria Lists'!$B:$B,-1),0),IFERROR(MATCH(9.99999999999999E+307,'Criteria Lists'!$B:$B,1),0))-1,1)
Also, it is legitimate for the range to be empty.
In this case Range("MyDynamicRange")(IndexVal)= "Test Value" will return err 1004.
So in the case where my named range is clear and I want to populate the first entry I have to explicitly populate cell $B$2.
After that the Named Range has data and the code will work.
I don't want to hard code the reference "$B$2" in my VBA as in
Range("$B$2")="First Entry"
So finally, here's the question....
Is it possible to return the column reference of an empty dynamic range without parsing the return value of the RefersTo property of the Names object
I've tried
?activeworkbook.Names("MyDynamicRange").RefersToRange.Address
which is ok if the range has data but returns err 1004 if it is blank
Sorry if my explanation is a bit long winded and thanks in advance,
Bill