Hi all,
I have an Excel spreadsheet with 10 columns, 7000 rows.
I would like to divide numbers (e.g. G3/G2) and apply formula every second row in Column G, H, I, and J.
Can you help me apply formula every second row all the way down to last row of data used?
Thanks!
Main.zip (2.5 KB)
Hello.
Since you have 7000 rows, my suggestion would be to use a combination of Select Range activity and the keystroke for Insert row.
Basically, you can select every other 2 rows by using an Enumerable method to create a list of ranges to use as the range. Then, Insert Row. After that, you can use Write Cell for each column to write the formula in only the first row (row 4). Finally, use another Select Range to select all cells needing the formula, and use the ctrl+d keystroke to fill down the formulas.
So, I’ll just throw that idea out there for now. I will follow up later with an .xaml file showing how to do that.
You can also instead use Invoke VBA if you have a little knowledge of Excel macros.
Regards.
If the data is in excel, my suggestion is to write a macro and run it on the file. Looping though 7000 rows, will have a disadvantage of increased processing time.
Thank you for instructing me, and waiting for your xaml file 
Hi. Sorry for the wait; it didn’t take very long but I got sidetracked.
Here was initial Excel file that I used: hoursamount.xlsx (8.0 KB)
Here was the result from running the .xaml: hoursamount.xlsx (9.1 KB)
Here is the .xaml file:
hoursamount.xaml (23.1 KB)
I provided some in-code documentation to help you navigate code. I also organized it in hopefully a very streamlined way to help with maintainability. And, like I said, if you are processing multiple files, then you can place the Excel actions inside a ForEach, and replace the srcFile variable that I used with what is being referenced to the file in both the Excel scope and the srcSelector variable where I assigned the selector string to concatenate the filename in it for a more dynamic approach.
Note: I forgot to annotate the variables, but the naming is hopefully descriptive enough. Let me know if you have any questions or how to integrate it further.
Regards.
Thank you so much. I really appreciate this. I couldn’t have managed without your help
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
We discovered that the Range has a limit of 255 characters, so the above approach was not going to work. Instead a Union inside a loop was needed in order to join that many rows together for the range.
I am providing this alternate solution using a .vbscript with the Invoke VBA activity. It’s a more technical solution but does work pretty well since it doesn’t rely on keystroke automation.
Here was my solution to @anna100
hoursamount.zip (3.8 KB)
As you can see in the screenshot, I am invoking the .vbs file and using the function that I called “InsertFormulas”. I used all the variables in as Parameters to it could execute based on the values that were stored in the workflow.
Let me know if you have any questions on that also.
Regards.
Hi Clayton. Thank you so much for getting this done. This is exactly what I was looking for 
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.
