bernatbosch
Board Regular
- Joined
- Dec 12, 2015
- Messages
- 66
Hi everybody!
Let's see if someone can help me understand this:
I have a table object in my worksheet (or many, but let's assume I have one, to make it easy).
The user will be able to add one or two elements above the table, which are meant to work together with the table's data, in a pair of ranges that have to be called 'table statistics' and 'table fields', respectively. Therefore, placed above the table, the user can choose to have one of these ranges, both ranges, or have just the table alone.
When the range called 'table statistics' is created (by a call of the user) the same procedure incorporates a cell at the top in which a title is added: "STATS". This cell is given a name that may help users to know what are the sheet and the table to which the range actually refers to (something like: 'Sheet1Table1StatsTitle').
A similar thing happens when the user creates the range called 'table fields': also a cell is being named (something like: 'Sheet1Table1FieldsTitle').
In case the 'statistics' range for this particular table does exist, the cell corresponding to StatsTitle name is always meant to be placed 20 rows above the table object.
In case the 'fields' range for this particular table does exist, the cell corresponding to FieldsTitle name is always meant to be placed 9 rows above the table object.
In order to apply certain procedure, one a table object is selected, I need to check whether the selected table has one of these ranges, both ranges, or the table is alone.
To do so, I try to check whether the cell placed 9 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the fields range. If not, I may consider the table has not the field's range.
Also, I try to check whether the cell placed 20 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the stats range. If not, I may consider the table has not the stats range.
Simple!
To check for the existance of these two names (named ranges's names) I use the following syntax:
If FieldsTitleRng.Name.Name <> vbNullString Then
and
If StatsTitleRng.Name.Name <> vbNullString Then
If the table being checked has not its fields range (fields range does not exist) the code returns an error, so I tell the procedure to go to certain line when this error occurs and there to note that the table has no fields range.
Untill this moment everyting works just fine.
BUT, once I've checked whether the selected table has fields range or not, when I try to check whether the selected table has stats range or not (by using the same procedure) in the case that in my first checking I got an error (so that fields range did not exist for the selected table) I get a new error that I'm not able to catch.
The result is that, under these circumstances (when the selected table has no names associated with it) in the checking for the existance of the second named range, I get the following message:
Run-time error '1004':
Method 'name' of object 'range' failed.
CAN ANYONE TELL ME WHY THE FIRST TIME I CHECK FOR A NAMED RANGE EXISTANCE USING THIS SYNTAX I CAN CATCH THE ERROR PRODUCED BY THE NON EXISTANCE OF THE NAME, BUT THE SAME SYNTAX DOES NOT ALLOW ME TO CATCH THE SAME ERROR FOR THE SECOND TIME?
Here is my code (neither FieldsTitleRng and StatsTitleRng have a name):
'check fields
On Error GoTo SelectedTableHasNoFields
Set FieldsTitleRng = TableObject.Range.Cells(1, 1).Offset(-9, 0).Resize(1, 1)
If FieldsTitleRng.Name.Name <> vbNullString Then 'Line that works fine
SelectedTableHasFields:
TableHasFields = True
Else
SelectedTableHasNoFields:
Err.Clear
TableHasFields = False
End If
'check stats
On Error GoTo SelectedTableHasNoStats
Set StatsTitleRng = TableObject.Range.Cells(1, 1).Offset(-20, 0).Resize(1, 1)
If StatsTitleRng.Name.Name <> vbNullString Then 'Line I get a Run-time error '1004': Method 'name' of object 'range' failed.
SelectedTableHasStats:
TableHasStats = True
Else
SelectedTableHasNoStats:
Err.Clear
TableHasStats = False
End If
Let's see if someone can help me understand this:
I have a table object in my worksheet (or many, but let's assume I have one, to make it easy).
The user will be able to add one or two elements above the table, which are meant to work together with the table's data, in a pair of ranges that have to be called 'table statistics' and 'table fields', respectively. Therefore, placed above the table, the user can choose to have one of these ranges, both ranges, or have just the table alone.
When the range called 'table statistics' is created (by a call of the user) the same procedure incorporates a cell at the top in which a title is added: "STATS". This cell is given a name that may help users to know what are the sheet and the table to which the range actually refers to (something like: 'Sheet1Table1StatsTitle').
A similar thing happens when the user creates the range called 'table fields': also a cell is being named (something like: 'Sheet1Table1FieldsTitle').
In case the 'statistics' range for this particular table does exist, the cell corresponding to StatsTitle name is always meant to be placed 20 rows above the table object.
In case the 'fields' range for this particular table does exist, the cell corresponding to FieldsTitle name is always meant to be placed 9 rows above the table object.
In order to apply certain procedure, one a table object is selected, I need to check whether the selected table has one of these ranges, both ranges, or the table is alone.
To do so, I try to check whether the cell placed 9 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the fields range. If not, I may consider the table has not the field's range.
Also, I try to check whether the cell placed 20 rows above the table object (first table column) has a name or not. If it has, I may consider the table has the stats range. If not, I may consider the table has not the stats range.
Simple!
To check for the existance of these two names (named ranges's names) I use the following syntax:
If FieldsTitleRng.Name.Name <> vbNullString Then
and
If StatsTitleRng.Name.Name <> vbNullString Then
If the table being checked has not its fields range (fields range does not exist) the code returns an error, so I tell the procedure to go to certain line when this error occurs and there to note that the table has no fields range.
Untill this moment everyting works just fine.
BUT, once I've checked whether the selected table has fields range or not, when I try to check whether the selected table has stats range or not (by using the same procedure) in the case that in my first checking I got an error (so that fields range did not exist for the selected table) I get a new error that I'm not able to catch.
The result is that, under these circumstances (when the selected table has no names associated with it) in the checking for the existance of the second named range, I get the following message:
Run-time error '1004':
Method 'name' of object 'range' failed.
CAN ANYONE TELL ME WHY THE FIRST TIME I CHECK FOR A NAMED RANGE EXISTANCE USING THIS SYNTAX I CAN CATCH THE ERROR PRODUCED BY THE NON EXISTANCE OF THE NAME, BUT THE SAME SYNTAX DOES NOT ALLOW ME TO CATCH THE SAME ERROR FOR THE SECOND TIME?
Here is my code (neither FieldsTitleRng and StatsTitleRng have a name):
'check fields
On Error GoTo SelectedTableHasNoFields
Set FieldsTitleRng = TableObject.Range.Cells(1, 1).Offset(-9, 0).Resize(1, 1)
If FieldsTitleRng.Name.Name <> vbNullString Then 'Line that works fine
SelectedTableHasFields:
TableHasFields = True
Else
SelectedTableHasNoFields:
Err.Clear
TableHasFields = False
End If
'check stats
On Error GoTo SelectedTableHasNoStats
Set StatsTitleRng = TableObject.Range.Cells(1, 1).Offset(-20, 0).Resize(1, 1)
If StatsTitleRng.Name.Name <> vbNullString Then 'Line I get a Run-time error '1004': Method 'name' of object 'range' failed.
SelectedTableHasStats:
TableHasStats = True
Else
SelectedTableHasNoStats:
Err.Clear
TableHasStats = False
End If