VBA Formula Help

[Jameo]

Hi guys,

I am trying to get VBA to enter a formula into my sheet. The VBA code is as follows:

Range("E4").Select
    ActiveCell.FormulaR1C1 = _
        "=INDEX((OFFSET(AvSort!A1,0,0,COUNT(AvSort!i:i),1)),D4,1)"

However, when the macro is ran, the code that gets inserted is:

=INDEX((OFFSET(AvSort!'A1',0,0,COUNT(AvSort!i:i),1)),'D4',1)

How do I get VBA to enter the code with the ' ' around A1 and D4, as with these symbols, the formula returns an error.

Many thanks for your help

 

[VoG]

Try replacing .FormulaR1C1 with .Formula

 

[jammer12001]

[FONT=Arial][SIZE=2][COLOR=#000000]Range("E4").Formula =[/COLOR][/SIZE][/FONT] "=index((offset(avsort!A!,0,0,COUNT(AvSort!I:I),1)),D4,1)"

<table width="64" border="0" cellpadding="0" cellspacing="0"><tbody><tr height="17"><td style="height:12.75pt;width:48pt" width="64" height="17">
</td></tr></tbody></table>

 

[Jameo]

[FONT=Arial][SIZE=2][COLOR=#000000]Range("E4").Formula =[/COLOR][/SIZE][/FONT] "=index((offset(avsort!A!,0,0,COUNT(AvSort!I:I),1)),D4,1)"

<TABLE cellSpacing=0 cellPadding=0 width=64 border=0><TBODY><TR height=17><TD style="WIDTH: 48pt; HEIGHT: 12.75pt" width=64 height=17>

</TD></TR></TBODY></TABLE>

Brilliant.

Thanks guys