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
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’
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.
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
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.
Apart from the macro you can also use data-table operations to write excel formulas.
Use read range store in a table and get the count of rows in your table.
Use a Add Data Column Activity and provide a column name and a Table Name if its a new table initialize as new Datatable.
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.
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.