Spire.XLS is a professional Excel API that enables developers to create, manage, manipulate, convert and print Excel worksheets. Get free and professional technical support for Spire.XLS for .NET, Java, Android, C++, Python.

Mon Mar 24, 2014 8:04 am

Hi

Is it possible to copy all formulas from one row to another?

Eg.
objWorkbook.Worksheets(0).Range("B6:FS6").Formula = objWorkbook.Worksheets(0).Range("B5:FS5").Formula


Thank you

newbieTeo
 
Posts: 3
Joined: Mon Mar 24, 2014 7:54 am

Mon Mar 24, 2014 9:04 am

Hello,

Thanks for your inquiry.

Sorry that it is impossible to copy all formulas from one row to another directly, and you only get all formulas by iterating the Range("B5:FS5"), then set it to corresponding cell in Range("B6:FS6").

If there are any questions, welcome to get it back to us.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Mon Mar 24, 2014 9:44 am

Thank you so much Gary.

I would like to loop thr the cells at Row 5, and copy the cell formulas to Row 6 - 50.
The following are the codes.
Is there a smarter way to copy the formulas without using the 'replace' function - to increment the row count?

'loop all cells at Row 5
For Each cell In objSheet1.Range("B5:FS5")
If cell.Formula > "" Then
'formula exists, apply same formula to Row 6 - 50
For iRow = 6 To 50
'Eg Cell formula: =SUMIF($AE$3:$AP$3,AQ$3,$AE5:$AP5)
objSheet1.Range(iRow, cell.Column).Formula = cell.Formula.Replace("5", iRow.ToString.Trim)
Next
End If
Next


Many Thanks

newbieTeo
 
Posts: 3
Joined: Mon Mar 24, 2014 7:54 am

Tue Mar 25, 2014 3:21 am

Hello,

Thanks for the reply.
You could change the formula.
Code: Select all
=SUMIF($AE$3:$AP$3,AQ$3,INDIRECT("$AE"&ROW()&":$AP"&ROW()))

If there are any questions, welcome to get it back to us.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Tue Mar 25, 2014 8:31 am

Thank you so much your help!

newbieTeo
 
Posts: 3
Joined: Mon Mar 24, 2014 7:54 am

Tue Mar 25, 2014 8:50 am

Thanks for your quick reply.

If there are any questions, welcome to get it back to us.

Sincerely,
Gary
E-iceblue support team
User avatar

Gary.zhang
 
Posts: 1380
Joined: Thu Apr 04, 2013 1:30 am

Return to Spire.XLS