Fastest way to write an excel formula into an entire column

hi @vvaidya,

do you have any solution on it?

1 Like

for me the best solution until now is:

Case 1: When you can work with excel tables when you write new rows normally will extend the formula properly.
Case 2: Just use a work around using copy paste, use the first row as “template” then copy it to a selected range it work fine with 1 to 1000 rows.

In case you are good with code… just go with code its 20000 better :slight_smile:

2 Likes

Yes I did thanks.

1 Like

Try to use invoke code instead and use this inside.

Worksheet.Range(“A1”).End(XlDirection.xlDown).Offset(1, 0).activate() 'Specifies last row
lRow = excel.ActiveCell.Row
Worksheet.Range(“C1”,“C”&lRow).Formula = “=A1+B1” ‘Insert the formula based on the range’

2 Likes

Hi @acaciomelo,

I need to calculate formula in excel-1 & I need to write the output in another excel
Can you suggest any way for that

2 Likes

Hi All,

I have a simple solution for this and i tried with 3 lakh records and it worked for me.

I used Invoke Code activity to do all this formula stuff.
Here is the code which i used.

  obj = wb.Sheets.Add(Before:=ws, 
Count:=1, 
 
  Type:=Microsoft.Office.Interop.Excel.XlSheetType.xlWorksheet) CType(obj, 
Microsoft.Office.Interop.Excel.Worksheet).Name = "Reversing Accruals"

   ws = CType(wb.Sheets("Reversing Accruals"), Microsoft.Office.Interop.Excel.Worksheet)
        ws.Activate()

        ws.Range("A1").Cells(1, 1) = "Projects with Reversing Accruals"
        count = 1
        With ws
            For j = 2 To ReverseAccrusalRange - 4
                ws.Range("A" + j.ToString).Formula = "=VLOOKUP(ReverseAccrusal!J" + j.ToString + ",Cost!$J$2:$K$" + CostRange.ToString + ",1,FALSE)"
         
            Next
        End With
5 Likes

I have used invoke code activity to apply formula to excel using below code it work fast for me, hope will do same for u.

Try

app = New Microsoft.Office.Interop.Excel.ApplicationClass
workbook= app.Workbooks.Open(FilePath)
worksheet = CType(workbook.Worksheets(“Sheetname”), Microsoft.Office.Interop.Excel.Worksheet)

Dim rows_Count As Integer = worksheet.UsedRange.Rows.Count

For i As Integer = 0 To rows_count

worksheet.Range(“Column to be entered” + i.ToString).FormulaLocal =“Formula to be entered here with = sign”

Next
app.DisplayAlerts = False
workbook.Save

range = Nothing
worksheet = Nothing

workbook.Close

workbook = Nothing
app.Quit
app = Nothing

Catch ex As Exception
End Try

3 Likes

Before sending the hotkey “Ctrl + D”, how to select the required range? I don’t need the whole column to be selected but just to the last row of my data. I tried “Select Range” then send hotkeys but doesn’t work.

Any idea?

2 Likes

Hello folks,

My requirement is to generate the Excel report every day. My doubt is once the execution is finished the excel row counter will back to its default value. If execution starts again on tommorow how does it know it should write the cell value in A4 or A5 etc. Could some suggest on the same

2 Likes

At the end of the workflow export the row number to an external file, like an excel or txt file.

The workflow can then pick this up every day as its starting row when the workflow is started again.

2 Likes

@Lizette

Where do we define the start and end range? Moreover, when I use this code, it gives an error that Sheet, direction, IRow, etc is not declared. Please advise.

Regards,
Deepak.

2 Likes

hi KEntwistle,

Can you let me know how you did it.
means used the macro in uipath

2 Likes

Apart from the macro you can also use data-table operations to write excel formulas.

  1. Use read range store in a table and get the count of rows in your table.
  2. Use a Add Data Column Activity and provide a column name and a Table Name if its a new table initialize as new Datatable.
  3. Use a For each row in the read range table create a counter and use a Add Data Row Activity pass the column name(created using Add Data Column) and DataTable name and the Data Row Array like example you want sum of two columns {“Sum(A2:B2)”} the write it as {“Sum(A”+YourRowCount.ToString+“:B”+YourRowCount.ToString+“)”} once all the rows are process the data is store into a table as created earlier now use the write range to write the formula column in excel.
2 Likes
1 Like

U can use Autofill Range. Much Quicker

Use invoke VBA instead of macro.
write code in text file with subroutine name then put that subroutine name in invoke VBA and give the path of that text file

The easiest way for this is to set formula for first cell using Write Cell Activity n then use the Auto Fill Range activity and mentioned the range over there, it will simply apply the formula to given range.

No for loop no send hotkey, it will get done with less response time.

which formula use and put Auto fill range activity

1 Like

–>Workbook Read Range
–>Excel application Scope
→ Write cell (Formula)
→ Fill Range ((C1)(C1:datatable.rows.count.tostring)

It works !​​​​​​​

1 Like

Great it works for me