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

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

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

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