in VBA Formula referencing different cells on different shee

[M151]

Hi,
My problem is this. I have a number of sheets populated with data via a series of calls to an oracle database through COMet and an access database via JET. I want to set up a formula in a column in the first sheet referencing two cells in the second sheet and another(different) cell in the first sheet. This is all being done thru VBA code for a large number of records in the second sheet. No absolute values can be used as the cells i have to reference in the second sheet change depending on the row im referencing on the first sheet. What i believe the formula should look like is this Worksheets("Fund Information").Cells(lPos, cSecTransCost)=
"=('Mandate Information'.Cells(lRow, cVarSellCost) * 'Fund Information'.Cells(lpos, csecvalue)) + 'Mandate Information'.Cells(lRow , cFixedSellCost)" where my first Sheet is called 'Fund Information' and my second sheet is called 'Mandate Information'. This fails at run-time. Ive tried setting it to
"=('Mandate Information'!R" & lRow & "C" & cVarSellCost * 'Fund Information'!R" & lpos & "C" & csecvalue) + 'Mandate Information'!R" & lRow & "C" & cFixedSellCost
but that fails at comile time
Ive tried setting the application.ReferenceStyle to xlR1C1 and xlA1 and neither version seems to work.
Any ideas???
(this is my first posting to this board so apologies if ive not included enough info or am using incorrect terminology)
Paul

 

[Andrew Poulsom]

It's a matter of trial and error with quotes and ampersands. Try this:

Sub Test()
    Dim lRow
    Dim cVarSellCost
    Dim lpos
    Dim csecvalue
    Dim cFixedSellCost
    lRow = 1
    cVarSellCost = 2
    lpos = 3
    csecvalue = 4
    cFixedSellCost = 5
    ActiveCell.FormulaR1C1 = _
        "='Mandate Information'!R" & lRow & "C" & cVarSellCost & "*'Fund Information'!R" & lpos & "C" & csecvalue & "+'Mandate Information'!R" & lRow & "C" & cFixedSellCost
End Sub