Default300
Board Regular
- Joined
- Jul 13, 2009
- Messages
- 83
Hi. Quick question I hope...
I am looking for the syntax to concatenate a string variable and a fixed text string to return a named range in a call to a range. (See "Problem" below.)
BACKGROUND / SCENARIO:
I have 2 Named Ranges.
Name 1 = "namFormCurUserDefSetting_ComboBox1"
Name 2 = "namFormDefUserDefSetting_ComboBox1"
Both have the same Suffix:
Name 1 Suffix = "_ComboBox1"
Name 2 Suffix = "_ComboBox1"
But the Prefix differs:
Name 1 Prefix = "namFormCurUserDefSetting"
Name 2 Prefix = "namFormDefUserDefSetting"
A Select Case Statement assigns either Prefix String 1 or Prefix String 2 to a String Variable called strSettingsChoice
PROBLEM:
What is the syntax to Concatentate the Prefix (a String Variable) and the Suffix (a string of text not assigned to a variable) to produce the complete Named Range Name to use in a Range Expression?
The main problem I am having is how to manage the Inverted Commas which must enclose the result of the concatenation (ie the complete Named Range Name), without making the Variable Name look like it is actually to be read as another text string.
Should I use single, double or treble inverted commas?
I'd prefer not to Chr(xxx) if possible.
Do I use them at the start and end of the concatention, or where?
DESIRED RESULT:
This is the fixed text code that works without error, which I would like to replace with the concatenation phrase.
UNSUCESSFUL ATTEMPT (1):
UNSUCESSFUL ATTEMPT (2):
All atempts so far have errored out with:
Run-time error '1004':
Method 'Range' of object '_Global' failed
Thanks in advance.
I am looking for the syntax to concatenate a string variable and a fixed text string to return a named range in a call to a range. (See "Problem" below.)
BACKGROUND / SCENARIO:
I have 2 Named Ranges.
Name 1 = "namFormCurUserDefSetting_ComboBox1"
Name 2 = "namFormDefUserDefSetting_ComboBox1"
Both have the same Suffix:
Name 1 Suffix = "_ComboBox1"
Name 2 Suffix = "_ComboBox1"
But the Prefix differs:
Name 1 Prefix = "namFormCurUserDefSetting"
Name 2 Prefix = "namFormDefUserDefSetting"
A Select Case Statement assigns either Prefix String 1 or Prefix String 2 to a String Variable called strSettingsChoice
PROBLEM:
What is the syntax to Concatentate the Prefix (a String Variable) and the Suffix (a string of text not assigned to a variable) to produce the complete Named Range Name to use in a Range Expression?
The main problem I am having is how to manage the Inverted Commas which must enclose the result of the concatenation (ie the complete Named Range Name), without making the Variable Name look like it is actually to be read as another text string.
Should I use single, double or treble inverted commas?
I'd prefer not to Chr(xxx) if possible.
Do I use them at the start and end of the concatention, or where?
DESIRED RESULT:
This is the fixed text code that works without error, which I would like to replace with the concatenation phrase.
Code:
[FONT=Courier New]
[/FONT][FONT=Courier New]
Case 1[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([COLOR=Red]"[/COLOR]namFormCurUserDefSetting[/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR]).Value
[/FONT][FONT=Courier New]
Case 2[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([COLOR=Red]"[/COLOR]namFormDefUserDefSetting[/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR]).Value
[/FONT]
Code:
[FONT=Courier New]
[/FONT][FONT=Courier New]
Case 1[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([COLOR=Red]"""[/COLOR] & strSettingsChoice & [COLOR=Red]"[/COLOR][/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR]).Value
[/FONT][FONT=Courier New]
Case 2[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([/FONT][FONT=Courier New][COLOR=Red]"""[/COLOR] & strSettingsChoice & [COLOR=Red]"[/COLOR][/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR]).Value
[/FONT]
Code:
[FONT=Courier New]
[/FONT][FONT=Courier New]
Case 1[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([COLOR=Red]""[/COLOR] & strSettingsChoice & [COLOR=Red]"[/COLOR][/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR] & [COLOR=Red]""[/COLOR]).Value
[/FONT][FONT=Courier New]
Case 2[/FONT]
[FONT=Courier New]
Me.ComboBox1.Value = Range([/FONT][FONT=Courier New][COLOR=Red]""[/COLOR] & strSettingsChoice & [COLOR=Red]"[/COLOR][/FONT][FONT=Courier New]_ComboBox1[/FONT][FONT=Courier New][COLOR=Red]"[/COLOR][/FONT][FONT=Courier New] & [COLOR=Red]""[/COLOR][/FONT][FONT=Courier New]).Value
[/FONT]
Run-time error '1004':
Method 'Range' of object '_Global' failed
Thanks in advance.
Last edited: