Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
One line is killing my program!
The following line works, where it specifies the first 4 characters of two
area codes (403, and (587. .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)" I need to substitute two variables - areaCode1 and areaCode2 to replace (403 and (587. I think I've done so, but the following doesn't work: .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""("" & areaCode1 , ""("" & areaCode2 },R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""("" &areaCode1 , ""("" & areaCode2 },R[0]C[-2])),0)),14)" I also need to replace a worksheet name, formerly "403" with "Do Not Call List". Have I done so, correctly, below? .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'403'!C[-4],0)" .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)" If someone would kindly point out my errors and how to fix them, I would be greatly appreciative. Jim Berglund |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
One line is killing my program!
.Range("C2:C" & q).FormulaR1C1 = "=MID(R[0]C[-2],INDEX(FIND({""(" & areaCode1 & """,""(" & areaCode2 & _ """},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(" & areaCode1 & """,""(" & areaCode2 & """},R[0]C[-2])),0)),14)" -- Gary Keramidas Excel 2003 "Jim Berglund" wrote in message ... The following line works, where it specifies the first 4 characters of two area codes (403, and (587. .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)" I need to substitute two variables - areaCode1 and areaCode2 to replace (403 and (587. I think I've done so, but the following doesn't work: .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""("" & areaCode1 , ""("" & areaCode2 },R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""("" &areaCode1 , ""("" & areaCode2 },R[0]C[-2])),0)),14)" I also need to replace a worksheet name, formerly "403" with "Do Not Call List". Have I done so, correctly, below? .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'403'!C[-4],0)" .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)" If someone would kindly point out my errors and how to fix them, I would be greatly appreciative. Jim Berglund |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
One line is killing my program!
..Range("C2:C" & Q).FormulaR1C1 = _
"=MID(R[0]C[-2],INDEX(FIND({""" & AreaCode1 & """,""" _ & AreaCode2 & """},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""" _ & AreaCode1 & """,""" & AreaCode2 & """},R[0]C[-2])),0)),14)" and ..Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)" looks ok to me. If it doesn't work correctly, then look for typos -- especially extra (leading/trailing) spaces in the worksheet name. Jim Berglund wrote: The following line works, where it specifies the first 4 characters of two area codes (403, and (587. .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)" I need to substitute two variables - areaCode1 and areaCode2 to replace (403 and (587. I think I've done so, but the following doesn't work: .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""("" & areaCode1 , ""("" & areaCode2 },R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""("" &areaCode1 , ""("" & areaCode2 },R[0]C[-2])),0)),14)" I also need to replace a worksheet name, formerly "403" with "Do Not Call List". Have I done so, correctly, below? .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'403'!C[-4],0)" .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)" If someone would kindly point out my errors and how to fix them, I would be greatly appreciative. Jim Berglund -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
One line is killing my program!
Thank you for your generous time in making this work. It does. I'm
DELIGHTED! Jim Berglund "Gary Keramidas" wrote in message ... .Range("C2:C" & q).FormulaR1C1 = "=MID(R[0]C[-2],INDEX(FIND({""(" & areaCode1 & """,""(" & areaCode2 & _ """},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(" & areaCode1 & """,""(" & areaCode2 & """},R[0]C[-2])),0)),14)" -- Gary Keramidas Excel 2003 "Jim Berglund" wrote in message ... The following line works, where it specifies the first 4 characters of two area codes (403, and (587. .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""(403"",""(587""},R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""(403"",""(587""},R[0]C[-2])),0)),14)" I need to substitute two variables - areaCode1 and areaCode2 to replace (403 and (587. I think I've done so, but the following doesn't work: .Range("C2:C" & q).FormulaR1C1 = _ "=MID(R[0]C[-2],INDEX(FIND({""("" & areaCode1 , ""("" & areaCode2 },R[0]C[-2]),MATCH(TRUE,ISNUMBER(FIND({""("" &areaCode1 , ""("" & areaCode2 },R[0]C[-2])),0)),14)" I also need to replace a worksheet name, formerly "403" with "Do Not Call List". Have I done so, correctly, below? .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'403'!C[-4],0)" .Range("E2:E" & q).FormulaR1C1 = "=MATCH(RC[-2],'Do Not Call List'!C[-4],0)" If someone would kindly point out my errors and how to fix them, I would be greatly appreciative. Jim Berglund |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Command line program use in VBA | Excel Programming | |||
Program for calculating a power trend line | Excel Programming | |||
Program line when charting variances? | Charts and Charting in Excel | |||
Program line when charting variances? | Excel Programming | |||
three line program... | Excel Programming |