Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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.. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
"passing" variable trough routine | Excel Programming | |||
VBA "Save As Text" Routine? | Excel Programming |