Insert apostrophe in VBA code as part of formula

[mosiki]

Hi

I wish to use VBA code to insert a formula into a spreadsheet. line of code below:

wsREC.Cells(j, lPosRecBrokMVCol).FormulaR1C1 = "=MATCH(RC[-6],INDIRECT(" ' "&RC[-7]&" Positions"&"'!k:k"),0)"

Problem is that the ' in between the "" is commenting the remainder of the line of code. is there any way to prevent this?

Thanks

Mosiki

 

[RSXchin]

post what you WANT the code to put in please.

 

[mosiki]

I want the code to put in the formula:

=MATCH(RC[-6],INDIRECT("'"&RC[-7]&" Positions"&"'!k:k"),0)

 

[Andrew Poulsom]

Try:

wsREC.Cells(j, lPosRecBrokMVCol).FormulaR1C1 = "=MATCH(RC[-6],INDIRECT(""'""&RC[-7]&"" Positions""&""'!k:k""),0)"

The easiest way to get the syntax is to record a macro while entering the formula.

 

[mosiki]

Double quotations worked, thanks.