How to copy a formula from one cell to a range of cells?

Aspose.Cells Product Family
1

How does one copy a formula from one cell to a range of cells? I have tried the following technique which neither errors out nor seems to do anything. I cannot find any examples on the Asppose site.

Dim NumberOfRows as Integer = 10
Dim r1 As Range = myWorkSheet.Cells.CreateRange(2, 5, 2, 5)
Dim r2 As Range = myWorkSheet.Cells.CreateRange(3, 5, 3+NumberOfRows, 5)
r2.Copy(r1)

Also, is there a convenient function to turn column letters (ex “AB”) into the appropriate column index (28) or do I have to roll my own?

Many thanks.

2

To copy formula, please try this piece of code:

Dim i As Integer
For i = 0 To 9
myWorksheet.Cells(3 + i, 5).Formula = myWorksheet.Cells(2, 5).Formula
Next

To get a column index from its name, please try CellsHelper.ColumnNameToIndex method.

3

Thanks. That gets me part of the way there. However, Aspose seems to have trouble handling quotation marks in formulas. A valid Excel formula, like:

=“soft”&"ware"

fails in Aspose.

Is there a way to work around this?

4

I don't find this problem.

Which version of Aspose.Cells are you using? Please try this attached version.

5

I am using Aspose.Excel. Can I get a temporary license for Aspose.Cells to see if it solves the problem?

6

This must be a problem in old version.

You don't need a license file to see if it solves the problem for Aspose.Cells doesn't have restriction on this feature.

However, if you do want to get a temp license, please post your request on http://www.aspose.com/Community/forums/220/ShowForum.aspx.

7

The good news is that this bug is fixed. Aspose.Excel clearly cannot handle quotation marks in formulas. Aspose.Cells can.

The bad news is that formula copying does not work the way I would like. What I would like is to have the formula automatically reference the correct row (copying =A1B1 becomes =A2B2, =A3*B3), etc… I can work around this by using string substitution, so this is not fatal. But this looks like basic functionality that should exist.

And I guess the other bad news is that we’d have to spend money on a new version just to fix a bug, when at the moment we have no other compelling reason to upgrade.

8

If you want to update reference while copying formulas, please try following piece of code:

Dim workbook As Workbook = New Workbook()

Dim myWorksheet As Worksheet = workbook.Worksheets(0)
Dim NumberOfRows As Integer = 10
Dim cell1 As Cell = myWorksheet.Cells("c1")

cell1.Formula = "=A1*B1"

Dim cell2 As Cell = myWorksheet.Cells("c2")
cell2.Copy(cell1)
workbook.Save("d:\test\abc.xls")

Please check your license file for the expiry date. If it's expired, you will have to renew the subscription for the update. Otherwise, you don't need to pay to use the new version.