Removal of " "'s in my VBA routine
 

code...
missing...
str1 = Right(activecell.Formula, Len(activecell.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1)
.....
End sub

I have (1st line above) that returns as a string the sheetname and cell
address.
vlaue returned -- '1'!$R143

str2 (line 3) returns '1'

The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str1 = "'1'"

a later line of code:
str4 = Sheets(str2).Range(str3).Formula

Does not return the proper sheet name '1' , but instead "'1'"

What can I do?
Tks in advance..

Removal of " "'s in my VBA routine
 

Correction: Below should read:
The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str2 = "'1'" <<< Original error - sorry..


"Jim May" wrote:

code...
missing...
str1 = Right(activecell.Formula, Len(activecell.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1)
....
End sub

I have (1st line above) that returns as a string the sheetname and cell
address.
vlaue returned -- '1'!$R143

str2 (line 3) returns '1'

The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str1 = "'1'"

a later line of code:
str4 = Sheets(str2).Range(str3).Formula

Does not return the proper sheet name '1' , but instead "'1'"

What can I do?
Tks in advance..

Removal of " "'s in my VBA routine
 

This line below
my str1 = "'1'"
should read
my str2 = "'1'"
Sorry...


"Jim May" wrote:

code...
missing...
str1 = Right(activecell.Formula, Len(activecell.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1)
....
End sub

I have (1st line above) that returns as a string the sheetname and cell
address.
vlaue returned -- '1'!$R143

str2 (line 3) returns '1'

The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str1 = "'1'"

a later line of code:
str4 = Sheets(str2).Range(str3).Formula

Does not return the proper sheet name '1' , but instead "'1'"

What can I do?
Tks in advance..

Removal of " "'s in my VBA routine
 

Maybe you could just check the first and last character of str2.

if left(str2,1) = "'" then
str2 = mid(str2,2)
end if
if right(str2,1) = "'" then
str2 = left(str2,len(str2)-1)
end if

And is there a reason you use instr() in one line, but worksheetfunction.find()
in another????

Why not just use VBA's instr() in both?



Jim May wrote:

code...
missing...
str1 = Right(activecell.Formula, Len(activecell.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1)
....
End sub

I have (1st line above) that returns as a string the sheetname and cell
address.
vlaue returned -- '1'!$R143

str2 (line 3) returns '1'

The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str1 = "'1'"

a later line of code:
str4 = Sheets(str2).Range(str3).Formula

Does not return the proper sheet name '1' , but instead "'1'"

What can I do?
Tks in advance..

--

Dave Peterson

Removal of " "'s in my VBA routine
 

Thanks Dave.
Got it..
Jim

"Dave Peterson" wrote:

Maybe you could just check the first and last character of str2.

if left(str2,1) = "'" then
str2 = mid(str2,2)
end if
if right(str2,1) = "'" then
str2 = left(str2,len(str2)-1)
end if

And is there a reason you use instr() in one line, but worksheetfunction.find()
in another????

Why not just use VBA's instr() in both?



Jim May wrote:

code...
missing...
str1 = Right(activecell.Formula, Len(activecell.Formula) - 1)
If InStr(str1, "!") = 0 Then Exit Sub
str2 = Left(str1, WorksheetFunction.Find("!", str1) - 1)
....
End sub

I have (1st line above) that returns as a string the sheetname and cell
address.
vlaue returned -- '1'!$R143

str2 (line 3) returns '1'

The problem is my sheet names are pure numbers (first 4 sheet names) 1 2 3
4
my str1 = "'1'"

a later line of code:
str4 = Sheets(str2).Range(str3).Formula

Does not return the proper sheet name '1' , but instead "'1'"

What can I do?
Tks in advance..

--

Dave Peterson