Excel Formula using OLE2 1

[krindler]

Hello.

Does anyone know of a way I can setup an Excel Relative Reference using OLE2 from Forms? I have loaded my data using OLE2, but now have a few subtotals I would like to insert, but without having to create a separate section for each cell.

I have a counter set up, but can't find a way to put the counter in when setting up my formula --> Rather than
'=SUM(F3:I3)' I would like the '3' to be the counter?

Any help you could give would be much appreciated.

Thanks In Advance.

 

[sfvb]

Are you talking about the R1C1 references or indirect references? Could you give an example.

 

[krindler]

Yes,

The R1C1 References.

 

[krindler]

Yes,

The R1C1 References. What I have now (which inserts the total into a cell correctly), is below:

args := OLE2.CREATE_ARGLIST;
OLE2.ADD_ARG ( args, 3 );
OLE2.ADD_ARG ( args, 10 );
cell := OLE2.GET_OBJ_PROPERTY
(worksheet, 'Cells', args );
OLE2.DESTROY_ARGLIST ( args );

OLE2.SET_PROPERTY
( cell, 'Formula', '=SUM( F3:I3 )' );
OLE2.SET_PROPERTY
( cell, 'NumberFormat', '$#,##0.00' );

OLE2.RELEASE_OBJ ( cell );

There are approximately 10 rows I need to do this subtotal for, so I would like to find a way to remove the 'F3:I3' and put in a relative reference depending on the number of rows returned.

Any help you can offer would be much appreciated.

Thanks,
Kelly

 

[sfvb]

I think this is what you want.

If your in cell H3, these two statments are functionally identical:
OLE2.SET_PROPERTY( cell, 'Formula', '=SUM( F3:I3 )' );
OLE2.SET_PROPERTY(cell, 'FormulaR1C1', '=SUM(RC[-4]:RC[-1]');

If your in cell H8, these two statments are functionally identical:
OLE2.SET_PROPERTY( cell, 'Formula', '=SUM( H4:H7 )' );
OLE2.SET_PROPERTY(cell, 'FormulaR1C1', '=SUM(R[-4]C:R[-1]C)');

 

[krindler]

Thanks so much for your help!! That fixed my problem!!

Kelly