Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Quotations in Formulas and strings

  Asked By: Ryan    Date: Sep 05    Category: MS Office    Views: 580
  

I am attempting to create a named range at run time by concatenating
strings and converting integer variables to strings. In this piece of
code, the string variable "Range_String" gives me a valid string (in
the watch window), but when I use it as an argument to add a new named
range to the worksheet I get an error. I know it has something to do
with using quotations in strings and formulas and can somehow can be
dealt with by employing single quotes in strategic places. However, I
can't quite get it to work.

Range_Name = "First"
Start = 1
'the active worksheet is named "Data_Sheet"
Range_String = "=Data_Sheet!$a$" & Str(start) & ":$c$20"
'trying to acheive "=Data_Sheet!$a$1:$c$20"

ActiveWorkbook.Names.Add Name:=Range_Name, RefersTo:=Range_String
Can anyone help?

Share: 

 

5 Answers Found

 
Answer #1    Answered By: Phoebe Brown     Answered On: Sep 05

It worked for me when I removed the Str() function:

Range_String = "=Data_Sheet! $a$" & Start & ":$c$20"

 
Answer #2    Answered By: Latoya Murray     Answered On: Sep 05

Your suggestion worked nicely. I'm not sure why
the Str() function causes an error  as the resultant string  itself looks like an
acceptable form for the argument  for that function. Do you know if the compiler
is automatically recasting the integer  variable to a string if the Str() is
omitted?

 
Answer #3    Answered By: Shobhana R.     Answered On: Sep 05

STR is the problem. For positive numbers, it inserts a space at the start
of the string.

This space can be seen in the locals window  or in the watch window.

You don't need to use STR to incorporate a number into a string. The &
operator will convert it for you anyway.

 
Answer #4    Answered By: Carl Woods     Answered On: Sep 05

I recall in the past, being unaware of the conversion, I
would use Trim(Str(Int) - I think this clumsy form would work  too.
Thanks for the explanation...

 
Answer #5    Answered By: Adal Fischer     Answered On: Sep 05

Yes, it would...............................

 
Didn't find what you were looking for? Find more on Quotations in Formulas and strings Or get search suggestion and latest updates.




Tagged:      

 
 
 
 

Related Post