Recording Macro - Insert Formula

[Bythsx-Addagio]

Excel 2003 VBA
Hi all,
I am attempting to write a macro to insert a formula into a specific cell.
I already have the formula written and working in a normal spreadsheet. I am
just trying to insert it exactly how it is in the same cell of several other
workbooks.

In order to get the syntax correct I tried recording a macro and "cut &
pasting" the formula. Normally this generate the VBA code with the right
syntax to use. However, for this particular formula I am getting an "Unable
to Record" error message. Is there something in the formula that is causing
a problem?? Thanks.

'48 Formula as taken from workbook
'=sum(INDEX(B11:F20,MATCH("Emerging Markets-Investment
Grade",B11:B20,0),MATCH("Net Market Weight
(%)",B11:F11,0)),INDEX(B11:F20,MATCH("Emerging Markets-High
Yield",B11:B20,0),MATCH("Net Market Weight (%)",B11:F11,0)))

Range("D4").Select
'Range("D5").FormulaR1C1 = _
"=SUM(INDEX(B11:F20,MATCH(""Emerging Markets-Investment
Grade"",B11:B20,0),MATCH(""Net Market Weight
(%)"",B11:F11,0)),INDEX(B11:F20,MATCH(""Emerging Markets-High
Yield"",B11:B20,0),MATCH(""Net Market Weight (%)"",B11:F11,0)))"

 

[EricG]

This may be the problem:

http://support.microsoft.com/kb/212172

HTH,

Eric

 

[jaf]

Hi,
Range("D4").Select
Range("D5").FormulaR1C1 = _
"=SUM(INDEX(B11:F20,MATCH(""Emerging Markets-Investment
Grade"",B11:B20,0),MATCH(""Net Market Weight
(%)"",B11:F11,0)),INDEX(B11:F20,MATCH(""Emerging Markets-High
Yield"",B11:B20,0),MATCH(""Net Market Weight (%)"",B11:F11,0)))"

I deleted the single quote and this code ran fine.

FYI: You're selecting D4 and pasting into D5. Don't know if that's what you want.

John