Help on how to insert a Formula into an Excel cell

HCL Domino Domino Forum
1

I have a LotusScript formula that searches for specific documents within a Db, resulting in a Document Collection. It then creates an Excel spreadsheet and enters data into the spreadsheet from each document in the Document Collection (project name, project catagory, hours worked per day on that project).

Everything works fine. But what I want to do now is put a formula into a cell that sums the columns, e.g. =SUM(E:3:E:15). I can even get that to work, sort of.

My problem is that the formula gets put into the cell as: =SUM(‘E:3’:‘E:15’) (single quotation marks around the cell names).

My question is how to get an Excel formula into a cell from LotusScript? I can refer to the columns by either letter or number, but I just can’t figure out how to do it correctly.

Here’s my snippet of code:

myFormula$="=SUM(E3:E"& Cstr(StartRow+i-1) & ")" '<---I know it will always start in Column,Row E3

mySheet.rows(startrow+i).NumberFormat="0" '<--I format the row cells as a number



Print myFormula$ '<----This prints =Sum(E3:E15)  (in my test, there were 13 rows of data)

mySheet.cells(StartRow+i,5).FormulaR1C1=myFormula$ '<–This puts the formula into the cell, but it comes out as: =SUM(‘E:3’:‘E:15’) and Excel reports that there is an error in the formula.

This feature is not a deal-breaker, but it would be a nice touch to add.

[this was revised to add the crucial line of code I forgot to put in when I posted originally]

2

Subject: Help on how to insert a Formula into an Excel cell

Instead ofmySheet.cells(5,4).value=myFormula$

try

mySheet.Range(“A1”).Formula=myFormula$

Cheers

Tim

3

Subject: Help on… I forgot the most important line of code

Sorry, I thought I was being so good in my description, but I forgot to put in the actual line of code where the formula is inserted into the spreadsheet:

this is the little snippet of code that sets the formula, then inserts that formula into a cell.

myFormula$=“=SUM(E3:E”& Cstr(StartRow+i-1) & “)” '<–This sets myFormula$ to the actual formula string

mySheet.rows(startrow+i).NumberFormat=“0” '<–I format the row cells as a number

Print myFormula$ '<----This prints in the status line: =Sum(E3:E15)

mySheet.cells(StartRow+i,5).FormulaR1C1=myFormula$ '<–This puts the formula into the cell

This puts the formula into the correct cell, but the formula ends up as: =SUM(‘E:3’:‘E:15’) (single quotation marks around the cell names).

I could sum the numbers in LS, however there are up to 16 columns of numbers,and a variable amount of rows, so it’s a lot easier to let Excel to do the math. (This is a time sheet report).

The data will always start in a specific cell, it’s just that the rows and columns will vary, depending on how many projects the person has worked on (rows), and how many days are in the time reporting period (between 14 and 16 columns).

I have previously put a formula into cells, but that formula didn’t require a range. I put the start date into one cell, then into the next cells of the row I put this formula to increment the dates in the columns:

For i = startColumn+1 To startColumn+myDays

mySheet.Cells(1,i).FormulaR1C1="=RC[-1]+1"

Next

I don’t know much about using LS with Excel, so my code might not be optimal, I just had to figure it out by example and testing. I have managed to set up an Excel spreadsheet with merged cells, borders, shading and page setup options, and I even sort specific rows…, but this one thing baffles me. The way I figured things out was to record a macro in Excel, doing the things I wanted to accomplish, then ‘convert’ that to LS.

I just created a macro to sum a set of cells and it seems to create the formula relative to the selected cell.

Maybe I have to do something similar (this is from my Excel macro):

Range(“C7”).Select

ActiveCell.FormulaR1C1 = "=SUM(R[-5]C:R[-1]C)

Thanks again.

OK, I figured it out.

I can do it the way my Excel macro did it (referring to cells by their relationship to the current cell).

( the variable i is the counter for the current (last) row where I want to insert the SUM formula, and there are 16 columns)

myFormula$= “=SUM(R[-” & Cstr(i) & “]C:R[-1]C)”

For y = 0 To 16

mySheet.cells(StartRow+i,5+y).FormulaR1C1=myFormula$

Next

4

Subject: Help on how to insert a Formula into an Excel cell

How about doing the calculation in your script and dropping the total into the spreadsheet instead?

5

Subject: Help on how to insert a Formula into an Excel cell

You can assign a formula to a range.

myFormula$=“=SUM(E3:E” & Cstr(StartRow+i-1) & “)”

mySheet.Range(“A1”).Formula = myFormula$

You can put the name of any cell in the Range index or even assign the range dynamically.

Hope that helps