One line is killing my program!

[Jim Berglund]

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

 

[Gary Keramidas]

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

 

[Dave Peterson]

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

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

 

[Jim Berglund]

Thank you for your generous time in making this work. It does. I'm
DELIGHTED!
Jim Berglund

.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

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