Default300
Board Regular
- Joined
- Jul 13, 2009
- Messages
- 83
Hi. I'd love some assistance with this problem I'm having. Thanks to those who've helped in the past. (And if you're interested, yes it is the same project!)
For the short version of this post, skip to "PROBLEM"!
I'm assigning a Named Range to be the RowSource of a ComboBox Control on a UserForm.
The Named Range is Dynamic (and, if relevant, Global).
It is a single rectangular area of contiguous cells.
Its width is fixed to the number of columns in the table.
But its height is dynamic and varies from [NB] ZERO ROWS to the maximum Rows in table (about 800), depending upon the result of another operation.
The other operation is AdvancedFilter xlFilterCopy method, where the CopyToRange is the table I've been talking about (ie the future RowSource).
Incidentally, since I am useing xlFilterCopy (NOT xlFilterInPlace), all rows in all tables are visible.
PROBLEM![Oops! :oops: :oops:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
If the CopyToRange has ZERO ROWS, then when I try to assign it as a RowSource I receive:
Run-time error '1004':
Method 'Range' of object '_Global' failed
TROUBLESHOOTING![Confused :confused: :confused:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
I have tried various ways to detect and circumvent the error (see code), but all of my attempts so far trigger the error themselves!
NAMED RANGES
I use very long descriptive names for my Named Ranges, to ensure that I or anyone coming after me can understand what they refer to.
I apologise if they are so long that it is hard to read the code.
There are just 2 Tables (Source and Destination).
SOURCE TABLE
Has 2 Named Ranges
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
All Records, Unfiltered
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
All Records, Unfiltered
DESTINATION TABLE
Has 4 Named Ranges
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
Filtered Records, (+ Blank Rows to bottom of table)
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
Filtered Records, (+ Blank Rows to bottom of table)
namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableInclHeader
Dynamic
1-801 Rows (Header Included In Range)
Filtered Records Only
namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader
Dynamic
0-800 Rows (Header Excluded From Range)
Filtered Records Only
IDEA & SOLUTION PREFERENCE![Oops! :oops: :oops:](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
As you'll see from the code, I'm using "with header" for the AdvancedFilter operation, but "without header" for the RowSource.
I wonder whether Dynamic Range "with header" could be used to detect a Range with 1 Row, since 0 Rows gives an Error.
I would prefer NOT to Exit Sub in the case of 0 Rows.
I would like to assign a different single row RowSource with a message such as "(No Records)" instead.
Therefore, I would like to step over the erroneous RowSource assignment (with If, If Not, or Case Select perhaps).
I have not yet tried On Error statements (but see code).
I will do do if required, but would prefer to code for this specific condition (zero rows), especially as I want to assign the single row RowSource in that situation only.
CODE
This is the code for the AdvancedFilter operation, followed by the RowSource assignment.
The commented code below that shows some of the failed attempts so far.
NAMED RANGE REFERS TO FORMULAE
For those interested, the RefersTo Formulae for the Dynamic Ranges are:
namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableInclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,0,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1)+1,COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))
namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableExclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,1,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1),COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))
Thanks to Ozgrid for the HowTo: [ http://www.ozgrid.com/Excel/DynamicRanges.htm ]
Thank in advance!![Wink ;) ;)](data:image/gif;base64,R0lGODlhAQABAIAAAAAAAP///yH5BAEAAAAALAAAAAABAAEAAAIBRAA7)
For the short version of this post, skip to "PROBLEM"!
I'm assigning a Named Range to be the RowSource of a ComboBox Control on a UserForm.
The Named Range is Dynamic (and, if relevant, Global).
It is a single rectangular area of contiguous cells.
Its width is fixed to the number of columns in the table.
But its height is dynamic and varies from [NB] ZERO ROWS to the maximum Rows in table (about 800), depending upon the result of another operation.
The other operation is AdvancedFilter xlFilterCopy method, where the CopyToRange is the table I've been talking about (ie the future RowSource).
Incidentally, since I am useing xlFilterCopy (NOT xlFilterInPlace), all rows in all tables are visible.
PROBLEM
If the CopyToRange has ZERO ROWS, then when I try to assign it as a RowSource I receive:
Run-time error '1004':
Method 'Range' of object '_Global' failed
TROUBLESHOOTING
I have tried various ways to detect and circumvent the error (see code), but all of my attempts so far trigger the error themselves!
NAMED RANGES
I use very long descriptive names for my Named Ranges, to ensure that I or anyone coming after me can understand what they refer to.
I apologise if they are so long that it is hard to read the code.
There are just 2 Tables (Source and Destination).
SOURCE TABLE
Has 2 Named Ranges
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
All Records, Unfiltered
namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
All Records, Unfiltered
DESTINATION TABLE
Has 4 Named Ranges
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader
Static
801 Rows (Header Included In Range)
Filtered Records, (+ Blank Rows to bottom of table)
namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableExclHeader
Static
800 Rows (Header Excluded From Range)
Filtered Records, (+ Blank Rows to bottom of table)
namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableInclHeader
Dynamic
1-801 Rows (Header Included In Range)
Filtered Records Only
namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader
Dynamic
0-800 Rows (Header Excluded From Range)
Filtered Records Only
IDEA & SOLUTION PREFERENCE
As you'll see from the code, I'm using "with header" for the AdvancedFilter operation, but "without header" for the RowSource.
I wonder whether Dynamic Range "with header" could be used to detect a Range with 1 Row, since 0 Rows gives an Error.
I would prefer NOT to Exit Sub in the case of 0 Rows.
I would like to assign a different single row RowSource with a message such as "(No Records)" instead.
Therefore, I would like to step over the erroneous RowSource assignment (with If, If Not, or Case Select perhaps).
I have not yet tried On Error statements (but see code).
I will do do if required, but would prefer to code for this specific condition (zero rows), especially as I want to assign the single row RowSource in that situation only.
CODE
This is the code for the AdvancedFilter operation, followed by the RowSource assignment.
The commented code below that shows some of the failed attempts so far.
Code:
[FONT=Courier New]Range("namSheetClientInfoUnfilteredStaticSortedFor1Sur1Loc1TableInclHeader").AdvancedFilter _[/FONT]
[FONT=Courier New] Action:=xlFilterCopy, _[/FONT]
[FONT=Courier New] CriteriaRange:=Range("namListsCriteriaRange"), _[/FONT]
[FONT=Courier New] CopyToRange:=Range("namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader"), _[/FONT]
[FONT=Courier New] Unique:=False[/FONT]
[FONT=Courier New]'(i) (ERROR occurs in NEXT LINE...)[/FONT]
[FONT=Courier New]Me.cboNavigateGotoClientSortedForename1Surname1Location1.RowSource = _[/FONT]
[FONT=Courier New] "namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader"[/FONT]
[FONT=Courier New]Me.cboNavigateGotoClientSortedForename1Surname1Location1.Value = Null[/FONT]
[FONT=Courier New]'(i) (Attempts to circumvent error, wrapped around error line...)[/FONT]
[FONT=Courier New]''If Not Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader") Is Nothing Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Not IsNull(Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader")) Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader").Rows.Count = 0 Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableInclHeader").Rows.Count > 1 Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Not Application.Intersect( _[/FONT]
[FONT=Courier New] Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader"), _[/FONT]
[FONT=Courier New] Range("namSheetClientInfoFilteredStaticSortedFor1Sur1Loc1TableInclHeader")) _[/FONT]
[FONT=Courier New] Is Nothing Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Not Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader") Is Nothing Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]''If Not IsNull(Range("namSheetClientInfoFilteredDynamicSortedFor1Sur1Loc1TableExclHeader")) Then[/FONT]
[FONT=Courier New]''End If[/FONT]
[FONT=Courier New]'(i) (I have not yet tried...)[/FONT]
[FONT=Courier New]''On Error Resume Next[/FONT]
[FONT=Courier New]''On Error Goto 0[/FONT]
NAMED RANGE REFERS TO FORMULAE
For those interested, the RefersTo Formulae for the Dynamic Ranges are:
namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableInclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,0,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1)+1,COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))
namSheetClientInfoFilteredDynamicSortedSur1For1Loc1TableExclHeader
=OFFSET(namSheetClientInfoFilteredStaticSortedSur1For1Loc1HeaderTopLeft,1,0,MATCH(1E+306,namSheetClientInfoFilteredStaticSortedSur1For1Loc1ColumnLeft,1),COLUMNS(namSheetClientInfoFilteredStaticSortedSur1For1Loc1TableExclHeader))
Thanks to Ozgrid for the HowTo: [ http://www.ozgrid.com/Excel/DynamicRanges.htm ]
Thank in advance!
Last edited: