Removal of " "'s in my VBA routine

[Jim May]

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..

 

[Jim May]

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]

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

 

[Dave Peterson]

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]

Thanks Dave.
Got it..
Jim

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?