? How do I: Concatenate String Variable with String/Text to Return Named Range Name (in Inverted Commas)?

[Default300]

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.

[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]

UNSUCESSFUL ATTEMPT (1):

[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]

UNSUCESSFUL ATTEMPT (2):

[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]

All atempts so far have errored out with:

Run-time error '1004':
Method 'Range' of object '_Global' failed

Thanks in advance.

 

[mikerickson]

Range does not require inverted commas. It does require a String as an argument.

Dim strSettingsChoice as String
strSettingsChoice = "namFormDefUserDefSetting"
Me.ComboBox1.Value = Range(strSettingsChoice & "_ComboBox1").Value

 

[Default300]

Solved: Concatenate String Variable with String/Text to Return Named Range Name (in Inverted Commas)?

Sorry. Solved it. Made a mistake.

"Unsuccessful" Attempt (2) was actually correct and therefore Successful.

In my real workbook, I had forgotten to remove the fixed text prefix.

That is, instead of:

Me.ComboBox1.Value = Range("" & strSettingsChoice & "_ComboBox1" & "").Value

I had:

Me.ComboBox1.Value = Range("" & strSettingsChoice & "[COLOR=Red]namFormCurUserDefSetting[/COLOR]_ComboBox1" & "").Value

Thanks anyone who's in the process of replying!!

Hope it helps others anyway.

 

[Default300]

Re: Range / Named Range Name doesn't require Inverted Commas

@mikerickson

Thanks. I didn't know that.

So are you saying that in my original fixed text code, I could have omitted the inverted commas?

Instead of:

    Me.ComboBox1.Value = Range([COLOR=Red]"[/COLOR]namFormCurUserDefSetting_ComboBox1[COLOR=Red]"[/COLOR]).Value

I could have used:

    Me.ComboBox1.Value = Range(namFormCurUserDefSetting_ComboBox1).Value

where namFormCurUserDefSetting_ComboBox1 is a Named Range.

 

[mikerickson]

Re: Range / Named Range Name doesn't require Inverted Commas

You could have used either

Me.ComboBox1.Value = Range("namFormCurUserDefSetting_ComboBox1").Value

or

Dim nameOfRange as String

nameOfRange = "namFormCurUserDefSetting_ComboBox1"
Me.ComboBox1.Value = Range(nameOfRange).Value

 

[Default300]

Re: Range / Named Range Name doesn't require Inverted Commas

Ok. Thanks.

So the string variable resolves to a string, the fixed text in inverted commas is also a string, and the concatenated result is a string, but the inverted commas are not required to surround the concatenated result.

With you now!

Was just checking VBA help, to see if that was where I picked up the habit. It was.

Thanks for your help now and in the past.