asked on

Excel OLE Automation Setting formula across range

I am creating and opening a delimited file that I then format in Excel through automation.

I need to apply a formula to a column that uses itself and the adjacent column (but next row down). It uses a value (seed) that appears at the bottom.

How do I do this through automation? I will not know how many rows I will have.

1 G H
2
3
4 100 =SUM(H8-G4)
5 200 =SUM(H8-G5)
6 0 =SUM(H8-G6)
7 150 =SUM(H8-G7)
8 1000 (seed value)
9
10

Thanks!

Jonathan

MichaelDS

go into your spread sheet and put a values in your grid. Then from the Tools menu select record a macro. Record exactly what you want to do. Then Select tools again. Stop recording. Then Select tools macros and Visual basic. go and look at the code that Excel has recorded here is your formula. By working this out this way rather than just being told you will discover that anything you can key into Excel you can also record as a marco and with a bit of practice you can convert it into general code.

Good Luck.

Michael

ASKER CERTIFIED SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

JonathanLewis

ASKER

Thanks for the responses. I posted the question as a VB/excell question as a figured I had a better chance at getting a solution that I could translate into what I needed. I ended up finding this solution:

//--- Loop through rows until we find a cell in "A" that is empty---------
Row = 4
while (objSheet.Range("A:A").Cells(Row).Text != "")
{
//--- Create formula--------------------------------------------------
Formula = "=SUM(I" + (Row+1) + "-" + "H" + (Row) + ")"
objSheet.Range("I:I").Cells(Row).Formula = Formula
//--- Look at next row------------------------------------------------
Row = Row + 1
}