I have a routine that takes a String

Sub headerInfo(name As String)

Range("A5").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=INDEX([Master.xls]Riders!R8C1:R39C11, MATCH(" & *name*
",[Master.xls]Riders!R8C1:R39C1,), MATCH(""Tiers
since"",[Master.xls]Riders!R8C1:R8C11,))"

ActiveCell.Formula = ActiveCell.Value

....more code


when I pass the string name, I do :

headerInfo name:="some string"

in order for the MATCH to work, I need to generate the following:

...MATCH(""some string"", ......

much like the second MATCH where it says ""Tiers / since"'"

how do I achieve this? right now is seem that it passes the name (som
string) with one set of quotes but I can't get it to pass 2 sets.

if I look at the formula that it generates in the workbook, it shows m
string without the quotes.

how do add a second set to my formula??

thanks

Pab

--
Message posted from http://www.ExcelForum.com

Sub TestName()
headerInfo "Richard"
End Sub

Sub headerInfo(sName As String)

Dim sFormula As String

sFormula = "=INDEX(Riders!R8C1:R39C11, MATCH(""" &
sName & """"
sFormula = sFormula & ",Riders!R8C1:R39C1,), MATCH
(""Tiers / since"",Riders!R8C1:R8C11,))"

With Selection

.FormulaR1C1 = sFormula
.Value = .Value
End With

End Sub

HTH
Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
I have a routine that takes a String

Sub headerInfo(name As String)

Range("A5").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False

ActiveCell.FormulaR1C1 = _
"=INDEX([Master.xls]Riders!R8C1:R39C11, MATCH(" &
*name* &
",[Master.xls]Riders!R8C1:R39C1,), MATCH(""Tiers /
since"",[Master.xls]Riders!R8C1:R8C11,))"

ActiveCell.Formula = ActiveCell.Value

....more code


when I pass the string name, I do :

headerInfo name:="some string"

in order for the MATCH to work, I need to generate the
following:

...MATCH(""some string"", ......

much like the second MATCH where it says ""Tiers /
since"'"

how do I achieve this? right now is seem that it passes
the name (some
string) with one set of quotes but I can't get it to
pass 2 sets.

if I look at the formula that it generates in the
workbook, it shows my
string without the quotes.

how do add a second set to my formula??

thanks

Pabs


---
Message posted from http://www.ExcelForum.com/

.

ahhhh,.,.

I was one set of quotes away from success!! :)

thanks


---
Message posted from http://www.ExcelForum.com/