in VBA Formula referencing different cells on different shee

M151

New Member
Joined
Nov 11, 2002
Messages
5
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
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
It's a matter of trial and error with quotes and ampersands. Try this:

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,232
Messages
5,594,956
Members
413,954
Latest member
mrsandy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top