passing strijng with double quotes

  • Thread starter Thread starter pabs
  • Start date Start date
P

pabs

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
 
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
 
Back
Top